Tip 1: How to auto fill a column in excel with VBA

Hi

So here is my first tip, and it is one I use regularly in the office as I produce all sorts of reports that need cells auto fill but I never know the number of cells to filldown as the data fluctuates daily.

In Excel, if you use the macro recorder to record a fill down you will notice that when looking at the macro afterwords it will have 'hard coded' the cell references of your example. See below...

Range("B1").Select
ActiveCell.FormulaR1C1 = "Test"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B48")
Range("B1").Select


As you can see the cell references on the autofill are hard coded and would therefor be of no use if you had say 200 rows of data because the autofill would only occur between rows 1 - 48.

In order to make the cell references variable we use a technique which starts at the bottom of the worksheet and finds the last row in it and uses this as your lower boundary.

Range("B1").Select
ActiveCell.FormulaR1C1 = "Test"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & Range("A65536").End(xlUp)
Range("B1").Select

What this code is now doing is saying ok, I want to auto fill the string "test" from cell B1 to... and then starting at the bottom of column A its looking up the worksheet to find the last cell with values in it and using this cell as the second reference.

As an example if you had a sheet with values in cells A1 through to A200 the code would autofill from B1:B200.