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:
There, the expression represents a Range object.
We will show you how these arguments are used in the examples below.
An example of AutoFill
In this example, we have a 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, based on 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 the full month name
Similarly, if you want to auto-fill the 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 the Type argument in the AutoFill function
You can use the Type argument to automatically fill Month names, Days, years, Linear trends, etc.
The 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 100th 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 column 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:
Similarly, you can use a series based on:
- 1
- 2
- 3
Or
- Prod_1
- Prod_2
- Prod_3
And so on.
Learn more about Type argument here:
https://learn.microsoft.com/en-us/office/vba/api/excel.xlautofilltype
