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.

Welcome to vba for dummies!

Hi

This blog has been created to offer a wealth of vba informaton including example scripts, code, and formulas for use at home or work. The content will be pitched at all levels and very easy to adopt. I will be updating with a weekly tip and posting any useful information I come across. I look forward to writing the blog and to you all reading it.

SM
VbaForDummies