0

Bit of context: I have a column which is mostly blank, but every now and then there will be a value in the cell (see attached). I start by entering a formula into the blank cell below the non-blank cell, and double clicking the bottom right corner does the trick easily. I can figure out how to loop this process in VBA, but how can I set the range in the autofill command to end at the next non-blank cell?

The column

cybernetic.nomad
  • 5,547
  • 3
  • 16
  • 26
Ptrckwu
  • 23
  • 4
  • Maybe take a look at [`Range.End`](https://docs.microsoft.com/en-us/office/vba/api/excel.range.end). – BigBen Dec 18 '19 at 20:51
  • If you would use `SpecialCells(12)` you will end up with something called `Areas`. You can loop those and set a formula for them in one go. – JvdV Dec 18 '19 at 21:28
  • You can enter the formula in the entire range in one go. No need to autofill. For Example`Range("A1:A" & LastRow).Formula = YourFormula` You can find the last row as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – Siddharth Rout Dec 19 '19 at 00:53

0 Answers0