Excel vba Autofill only empty cells

0
votes

I have a column A with data up to A300.

In this range, some of theses cells are empty, some contain values.

In VBA, I set the formula of the cell A1 then I use the autofill function to set it all over my column (up to A300) like this :

ws.Range("A1").Select
Selection.AutoFill Destination:=ws.Range(ws.Cells(1, 1), ws.Cells(300, 1))

My problem is that datas contain on some cells are erased too ! I'm trying to autofill like it but only throught the empties cells.

I tried to add a filter on my worksheet like this :

ws.Range("$A$1:$A$300").AutoFilter Field:=1, Criteria1:="="

Then I reused the autofill function, but it seems to fill thourght the filtered cells...

Can't we add a parameter like "only empties cells" to the autofill function ? Something like this :

Selection.AutoFill Destination:=ws.Range(ws.Cells(1, 1), ws.Cells(300, 1)), Criteria1:="="

Thanks for your replies !

4
vbaexcelautofill
What are you trying to fill them with? Static or progressive values? See the Range.SpecialCells method with xlCellTypeBlanks. – user4039065
I have an other problem with the "SpecialCells(xlCellTypeBlanks)". It results that If copy then paste an empty cell, these cell is no longer considered as a blank cell, and it isn't selected by this code (but this cell still empty) it's an already knew issue of the SpecialCells(xlCellTypeBlanks). So I can get the error "No cell match" even if I have a blank cell. Do you know any solution for this problem ? – M. Ozn

4 Answers

1
votes

Apologies, I miss-understood you question - Want to fill all blank cells with the value in A1? - here you go:

Sub Replace()

If Trim(Range("A1").Value) = "" Then
    MsgBox "There's no value in A1 to copy so there's nothing to copy to all blank cells", vbInformation, "Nothing in A1"
    Exit Sub
    Else
        Range("A1:A300").SpecialCells(xlCellTypeBlanks).Select
        Selection.Value = Range("A1").Value
End If

End Sub
3
votes

with data like:

enter image description here

I would do a single copy rather than a fill-down:

Sub luxation()
    Range("A1").Formula = "=ROW()"
    Dim rDest As Range
    Set rDest = Intersect(ActiveSheet.UsedRange, Range("A1:A300").Cells.SpecialCells(xlCellTypeBlanks))

    Range("A1").Copy rDest
End Sub

with this result:

enter image description here

NOTE:

The formulas adjust after being copied.

EDIT#1:

Please note that there are some circumstances under which this code will not work. It is possible that UsedRange my not extend down to cell A300.

For example, if the worksheet is totally empty except for a formula in A1 and some value in A3. In this case Rdest will only include the single cell A2. The code will leave A4 through A300 untouched.

2
votes

Assuming you want static values, I would use a loop. The one below will fill all empty cells with poop:

Sub AllFillerNoKiller()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

For Each c In ws.Range("A1:A300")
    If c.Value = "" Then c.Value = "poop"
Next
End Sub
1
votes

You can also use below code:

stAddress = Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Address
Sheet1.Range(st).Value = "Empty"