Using VBA in Excel can greatly increase speed and productivity of the majority of Excel projects and systems. Even in VBA, shortcuts and usual Excel ‘quick wins’ can be used to your advantage whether that be the increased speed of doing a task, or just making something easier to do.
The auto fill functions of Excel can be put to good use regularly on Excel especially when there is a large amount of data you are working with. Drag the formulas down or double click the bottom right cell of the furthest right cell in your selection – it all does the same.
Auto fill in VBA is just a way of carrying out this procedure and is perfect for when complex (or even easy) formulas are required in a column or range. Putting the formula in the top cell and applying auto fill by VBA works along the following code. Although this code is set to auto fill the formula in cell A1 all the way down to 500, you can set this range to any you require (the selection and first cell must be where the formula is held). We have also included a short example of code when the range may be dynamic and you simply want to auto fill down to the last used row in the range.
Feel free to ask us a question, or post in the comments below.
Public Sub Auto_fill_Downwards()
Selection.AutoFill Destination:=Range(“A1:A500”), Type:=xlFillDefault
Public Sub Auto_fill_to_lastrow()
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range("A1:A" & lastrow), Type:=xlFillDefault