CODEWITHSUNDEEP
vbaexcel
sretko
sretko

Reputation: 640

VBA - autofill and increment from the last non - empty cell

I have the following code:

Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("A" & Cells.Rows.Count).End(xlUp).AutoFill Destination:=Range("A2000:A" & lastRow)

It works fine. It finds the the last non - empty cell in column B and then starts to Autofill column A from A2000 to the cell in question in column B. However I need it to start AutoFill from the last non - empty cell in column A even if there is only one empty row to be auto-filed. This is important as usually I have only one empty cell between column A and column B, which means there may be other decision as AutoFillNext or similar. I tried to modify the code just by removing the cell number, like this:

Destination:=Range("A:A" & lastRow)

It doesn't work as expected.

Upvotes: 1

Views: 1865

Answers (1)

user4039065
user4039065

Reputation:

I tend to use the Range.FillDown method rather than the Range.AutoFill method.

Dim lastRow As Long
with activesheet
    lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range(.Cells(.Rows.Count, "A").End(xlUp), .Cells(lastRow, "A")).FillDown
end with

If you are trying to increment a date value then a Range.DataSeries would be more appropriate.

Dim lastRow As Long
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    With .Range(.Cells(.Rows.Count, "A").End(xlUp), .Cells(lastRow, "A"))
        .DataSeries Rowcol:=xlColumns, Type:=xlChronological, _
                    Date:=xlDay, Step:=1, Trend:=False
    End With
End With

Upvotes: 2

Related Questions