Jul 07, 2015

Sometimes you require imitating the operation of Autofill to the last cell in Excel VBA. This is when you double click the little dot located at the right bottom corner of the active cell in Excel, i.e. cell D2 in the picture below;

By double clicking the dot, Excel Auto fills the column all the way down to the last row of its adjacent column. In the picture above it will fill it down to row 10 because that is the last row of its adjacent left column.

Here’s the code in VBA;

Only one line of code;

Before explaining that code, you need to know two important facts

• AutoFill requires a range so for example;
• ActiveCell.Range(“D2:D10”) will fill the range D2:D10 automatically starting from ActiveCell which should be D2.

The Range() object has two forms

1. Range(“D2:D10”) where you provide a text to indicate the range
2. Range(Cell(2,4),Cell(10,4)) where you provide the top-left most cell and right-bottom most cell of the range; e.g. Range(D2,D10)

In the code above we have used the second form of Range object i.e.

Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))

Where the cell specified in red is the top-left cell which is the ActiveCell, in our case D2, the cell in blue is the bottom-right cell which eventually should be D10 but we need to calculate that.

The calculation is very simple. What we do first is we move one column to the left from the active cell. That will put us in cell C2. This is done by writing ActiveCell.Offset(0,-1)

We then move down to the last cell which is done by .End(xlDown) bit. This indicates cell C10

We should now move back one column to the right by using Offset(0,1) to get to D10.

So here we have a generic line of code that we can use to Auto Fill down from any active cell.

If you wanted to expand this code to fill to the last row from the RIGHT adjacent column just change the code to;

ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown).Offset(0,- 1))

Notice the column number in Offset() have changed.

If you want it to fill upwards to the first row from the right adjacent column then I’m sure you know how to alter the code but I still write it here;

ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlUp).Offset(0,- 1))

And of course any variation will work.

Happy Coding

Cyrus Mohseni