VBA - How to Autofill all the way to the bottom until the last B cell with data

Question

I recorded a macro and I tried to autofill column C with the COUNTIF function all the way down until B column has their last cell value. It doesn't work with new data so I tried to edit the macro and replace it with Range("D2:D") but that doesn't work.

Sub COUNTIF()
'
' COUNTIF Macro
'

'
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-3])>1"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D")
End Sub

[Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and use that in your range reference. — BigBen, May 05 '20 at 17:17

score 0 · Accepted Answer · answered May 05 '20 at 17:26

Better to find the last cell. Also avoid using select as it slows down your code.

Sub COUNTIF()

Dim LastRowB As Long
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
Range("D2").FormulaR1C1 = "=COUNTIF(C[-3],RC[-3])>1"
Range("D2: " & "D" & LastRowB).FillDown

End Sub

Now change your "=COUNTIF(C[-3],RC[-3])>1" formula to whatever it is you really want to fill down.

VBA - How to Autofill all the way to the bottom until the last B cell with data

1 Answers1