CODEWITHSUNDEEP
vbaexcelautofill
M. Ozn
M. Ozn

Reputation: 1220

Excel vba Autofill only empty cells

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 !

Upvotes: 0

Views: 5691

Answers (4)

Jeremy
Jeremy

Reputation: 1337

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96763

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.

Upvotes: 4

jitender
jitender

Reputation: 9

You can also use below code:

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

Upvotes: 1

Preston
Preston

Reputation: 8187

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

Upvotes: 2

Related Questions