Excel / VBA Autofill Method

A little about AutoFill Method in VBA

AutoFill is a pretty useful method in VBA that can be used to auto-fill cells in the specified range.

In certain scenarios, you have to copy-paste cell contents to many other cells in the same sheet or some other sheet.

The Range’s AutoFill method makes it quite simple where you need to specify the source range and destination range as shown in the syntax below:

expression.AutoFill (Destination, Type)

There, the expression represents a Range object.

We will show you the usage of these arguments in the examples below.

An example of AutoFill

In this example, we have Range of two cells (A1:A2). This will act as the source range in the AutoFill method:

We will use it to auto-fill cells from A3:A20 cells.

VBA code:

Sub autofill_ex()

Dim srcRng

Dim tgtRange


'Source of range used to fill

Set srcRng = Worksheets("Sheet1").Range("A1:A2")


'Destination Range

Set tgtRange = Worksheets("Sheet1").Range("A1:A20")


'Executing AutoFill

srcRng.AutoFill Destination:=tgtRange


End Sub

Result:

You can see, on the basis of the first two cells, we filled up to A20 cells.

Filling months by AutoFill

See the sheet below where we have only Jan, Feb, and Mar in A2 to A5 cells:

VBA code to Auto Fill all months:

Sub autofill_ex()


Dim srcRng_Mon

Dim tgtRange_Mon

'Source of range used to fill

Set srcRng_Mon = Worksheets("Sheet1").Range("A2:A4")


'Destination Range

Set tgtRange_Mon = Worksheets("Sheet1").Range("A2:A13")


'Executing AutoFill

srcRng_Mon.AutoFill Destination:=tgtRange_Mon


End Sub

Result:

Auto fill full month name

Similarly, if want to auto-fill full month name then give the full Month name to a cell (at least).

In the example below, we are only using one cell that contains “January”:

Code:

Sub autofill_ex()

Dim srcRng_Mon_Full

Dim tgtRange_Mon_Full


'Source of range used to fill

Set srcRng_Mon_Full = Worksheets("Sheet1").Range("A2")


'Destination Range

Set tgtRange_Mon_Full = Worksheets("Sheet1").Range("A2:A13")

'Executing AutoFill

srcRng_Mon_Full.AutoFill Destination:=tgtRange_Mon_Full


End Sub

Result:

Exploring Type argument in the AutoFill function

You can use the Type argument to fill Month names, Days, years, Linear trends, etc. automatically.

Following are a few examples of using this argument.

Filling Weekday example by Type argument

Sub autofill_ex()


Dim srcRng_Day_Short

Dim tgtRange_Day_Short


'Source of range used to fill

Set srcRng_Day_Short = Worksheets("Sheet1").Range("A2")

'Destination Range

Set tgtRange_Day_Short = Worksheets("Sheet1").Range("A2:A14")

'Executing AutoFill

srcRng_Day_Short.AutoFill Destination:=tgtRange_Day_Short, Type:=xlFillWeekdays


End Sub

Output:

Auto-fill format example

Similarly, cell formatting can be auto-filled by using Type argument’s xlFillFormats value. Consider we have the following formatting in three cells that we want to replicate to the 100^th cell:

Code:

Sub autofill_ex()

Dim srcRng_Format

Dim tgtRange_Format


'Source of range used to fill

Set srcRng_Format = Worksheets("Sheet1").Range("A2:A4")


'Destination Range

Set tgtRange_Format = Worksheets("Sheet1").Range("A2:A100")


'Executing AutoFill

srcRng_Format.AutoFill Destination:=tgtRange_Format, Type:=xlFillFormats

End Sub

Result:

Using xlFillSeries in Type argument

You may also auto fill a range by series. For example, A columns cell has employee Id like:

Emp001

Emp002

Emp003

And you want to fill it till Emp100

VBA code:

Sub autofill_ex()

Dim srcRng_Series

Dim tgtRange_Series


'Source of range used to fill

Set srcRng_Series = Worksheets("Sheet1").Range("A2:A4")


'Destination Range

Set tgtRange_Series = Worksheets("Sheet1").Range("A2:A100")


'Executing AutoFill

srcRng_Series.AutoFill Destination:=tgtRange_Series, Type:=xlFillSeries


End Sub

Result: