I am trying to AutoFill a formula, to the last possible row. It should go until the same cell / row, like if I would double-click.
The adjacent row, with has data, which the current AutoFill should follow, has a cut in the data, and it continues after with another table. I want to AutoFill until that cut, and not to the very last value in the adjacent column.
When you see the screenshot, you can understand more. In the UI Excel, when I double click on the cell AD12, the formula gets dragged until cell AD24. That’s exactly what I want to do with VBA
When I record the VBA, it gives me a hardcoded rows number (See below code generated – Range(“AD12:AD24”)). I want it dynamic, because the table is growing. How to autofill until that AD24 cell, but AD24 should be got dynamically not hardcoded.
Range("AD12").Select
Selection.AutoFill Destination:=Range("AD12:AD24")
Range("AD12:AD24").Select
2
Try the following, which is the equivalent of ctrl-down from AE12 to find the last row (which is basically what is happening if you double click the cell’s handle):
Dim lastrow As Long
lastrow = Range("AE12").End(xlDown).Row
Range("AD12").AutoFill Destination:=Range("AD12:AD" & lastrow)
Of course, if you’re using VBA to populate AD12 anyway, you can instead do both by replacing that code with something like this:
Dim lastrow As Long
lastrow = Range("AE12").End(xlDown).Row
Range("AD12:AD" & lastrow).Formula2 = "<your_formula>"
5