Autofill is a common feature of Microsoft Excel. When we want to spread any value, formula through left to right or up to bottom we use this autofill method. This autofill feature is also available in Excel VBA. But sometimes when we perform this autofill operation, we get **Run-time error ‘1004’**. In this article, we will discuss this autofill method of range class failed. We will solve this problem.

**What is Autofill Method in Excel VBA?**

The **VBA autofill** comes when we need to fill the formula of the start cell to the last cell within a column or row. We normally apply any formula in the first cell. We may copy and paste that formula to the last cell. Otherwise, we may autofill by double click. Or we can drag the cursor up to the bottom or left to right. In **VBA**, we can do this work by the autofill method.

Before using the autofill in **VBA**, we need to understand the syntax of this method.

**Syntax:**

*AutoFill(Destination As Range,(Type As XlAutoFillType=XlFillDefault)*

**Argument:**

ARGUMENT | REQUIRED | EXPLANATION |
---|---|---|

Range |
REQUIRED |
This is the reference that will be used for autofill. |

Destination |
REQUIRED |
This is the destination where the autofill will occur. |

Type As XlAutoFillType |
REQUIRED |
This is the series of fill types. From a list, we will select the type. |

**How to Apply AutoFill Method in VBA?**

In this section, we will show how to apply **AutoFill** in **VBA**.

We will fill names of days and months in the following data set.

**Step 1:**

- First, go to the
**Developer**tab. - Then choose
**Record Macro**.

**Read More: [Fixed!] Auto Fill Options Not Showing in Excel**

**Step 2:**

- Named the macro
**AutoFill1**. - Then, press
**OK**.

**Read More: How to Turn Off AutoFill in Excel**

**Step 3:**

- Now, put
**Monday**on**Cell B5**. This will be considered as the reference for autofill.

**Read More:** **[Solved:] Excel Double Click AutoFill Not Working**

**Step 4:**

- Now, go to the command module of the
**VBA**. - Write down the below code:

```
Sub AutoFill1()
Dim source As Range, target As Range
Set source = Range("B5")
Set target = Range("B5:B11")
source.Autofill Destination:=target, Type:=xlFillDays
End Sub
```

**Step 5:**

- Now, press
**F5**to run the code.

Here, we get all the day’s names using the autofill. In the code, we used **“ xlFillDays ” **as we are working with the day.

We will show how to autofill the months below.

**Step 6:**

- Create another macro named
**Autofill2**.

**Read More:** **Drag Number Increase Not Working in Excel**

**Step 7:**

- Now, type the below code in the command module.

**Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table**

**Step 8:**

- Again, press
**F5**to run the code.

Now, we get the month’s name here using the autofill feature.

**Read More: Fix: Excel Autofill Not Working**

**AutoFill Method of Range Class Failed: 3 Cases with Fixes**

Here we will discuss the autofill method of range class failed problems and their solution.

**1. Autofill Method of Range Class Failed Vertically and Horizontally**

Assume that we want to autofill vertically and horizontally in both ways at the same time. In this section, we will show which problems we may face while dealing with them and how to solve them.

In the above data set, we will autofill data horizontally and vertically.

First, we will autofill data horizontally i.e. from left to right.

Now, we will write the below code to autofill left to right.

```
Sub Autofill3()
Dim source As Range, target As Range
Set source = Range("B4")
Set target = Range("C4:E4")
source.Autofill Destination:=target, Type:=xlFillCopy
End Sub
```

Then, press **F5** to run the code.

Here, an error message is there which says **“ AutoFill method of Range class failed”**.

**Solution**

Now let’s see how to solve the issue.

**Step 1:**

- Now, change argument
**C4**to**B4**and the code becomes:

```
Sub Autofill3()
Dim source As Range, target As Range
Set source = Range("B4")
Set target = Range("B4:E4")
source.Autofill Destination:=target, Type:=xlFillCopy
End Sub
```

**Step 2:**

- Then, press
**F5**to run the code.

We can see that the data autofill toward the right cells as mentioned in the code.

Now, we will autofill the from the top to bottom as per our desire.

**Step 3:**

- Modify the previous code like below:

** **

**Step 4:**

- Press
**F5**to run the code.

Like the previous section, here an error is showing indicating the **Autofill method of Range class failed**.

**Step 5:**

- Now, change the
**Range**argument from**B5**to**B4**.

**Step 6:**

- Again, press
**F5**to run the code.

Now, we can see that the data is auto-filled both horizontally and vertically separately.

**Similar Readings:**

**How to Auto Populate from Another Worksheet in Excel****How to Perform Predictive AutoFill in Excel****Applications of Excel Fill Series****[Fix] Excel Fill Series Not Working****AutoFill Formula to Last Row with Excel VBA****How to Autofill a Column in Excel****How to Fill Column in Excel with Same Value****How to Fill Down to Last Row with Data in Excel****How to AutoFill Formula When Inserting Rows in Excel****Filling a Certain Number of Rows in Excel Automatically**

**2. AutoFill Method of Range Class Failed Horizontally and Vertically ****Simultaneously**

Here, we will show how to autofill horizontally and vertically at a time.

Here, we will show how to autofill vertically and horizontally simultaneously of the above data set.

Write down the below code on the **VBA** command module:

```
Sub AutoFill4()
Dim source As Range, target1 As Range, target2 As Range
Set source = Range("B4")
Set target1 = Range("C4:F4")
Set target2 = Range("B5:B11")
source.Autofill Destination:=target1, Type:=xlFillCopy
source.Autofill Destination:=target2, Type:=xlFillCopy
End Sub
```

Now, press **F5** to run the code.

Here,** AutoFill method of Range class failed** error is showing. Now, we will solve this error by modifying the code.

**Solution**

Now we will solve this problem by modifying the code.

**Step 1:**

- Modify the code like below in the
**target1**and**target2**

```
Sub AutoFill4()
Dim source As Range, target1 As Range, target2 As Range
Set source = Range("B4")
Set target1 = Range("B4:F4")
Set target2 = Range("B4:B11")
source.Autofill Destination:=target1, Type:=xlFillCopy
source.Autofill Destination:=target2, Type:=xlFillCopy
End Sub
```

**Step 2:**

- Run the code by pressing the
**F5**button.

Here, the autofill method is applied on both sides simultaneously.

**Read more: How to Increment Month by 1 in Excel**

**Similar Readings: **

**How to Autofill Dates in Excel Without Dragging****How to Autofill Days of Week Based on Date in Excel****How to AutoFill Months in Excel****How to Create Automatic Rolling Months in Excel****How to Enter Sequential Dates Across Multiple Sheets in Excel****How to Auto Generate Number Sequence in Excel****How to Add Sequence Number by Group in Excel****How to Repeat Number Pattern in Excel****Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows****How to Autofill Dates in Excel**

**3. Solved AutoFill Range Class Failed Problem with Desired Cells**

In this section, we will show, how to autofill data in desired positions vertically and horizontally.

We will autofill on this data set horizontally and vertically in a certain position as we need.

**Step 1:**

- Now, create a macro named
**AutoFill5**.

**Step 2:**

- Put the below code on the command module.

```
Sub AutoFill5()
Dim source As Range, target As Range
Set source = Range("B6")
Set target = Range("C4")
source.Copy target
Set source = Range("C4")
Set target = Range("C4:F4")
source.Autofill Destination:=target, Type:=xlFillCopy
Set source = Range("C4:F4")
Set target = Range("C4:F11")
source.Autofill Destination:=target, Type:=xlFillCopy
End Sub
```

**Step 3:**

- Press the
**F5**button to run the code.

Here, we can see that the data is auto-filled on the desired locations.

**Read More: How to Auto Number or Renumber after Filter in Excel**

**Some Examples of AutoFill Method in Excel VBA**

Here, we will show some examples of the autofill methods in Excel **VBA**.

**1. AutoFill Same Format**

Here, we will show how the same format is filled to other cells using the autofill feature.

We will show that the above image with the mentioned color format will be auto-filled in the below cells.

**Step 1:**

- Create a new macro named
**AutoFill6**.

**Step 2:**

- Put the below code on the command module.

```
Sub AutoFill6()
Dim source As Range, target As Range
Set source = Range("B4:B6")
Set target = Range("B4:B12")
source.Autofill Destination:=target, Type:=xlFillFormats
End Sub
```

**Step 3:**

- Now, press
**F5**to run the code.

Here, we auto-filled with the given format.

**Similar Readings:**

**How to AutoFill Numbers in Excel****How to Auto Number Cells in Excel****How to Autofill Numbers in Excel Without Dragging****How to AutoFill Ascending Numbers in Excel****How to AutoFill Numbers in Excel with Filter**

**2. AutoFill Method with Desired Option**

In this section, we will show how to get the desired type of object using the autofill feature.

We will use this data set. We want to get only the numeric values.

**Step 1:**

- Create a new macro named
**AutoFill7**.

**Step 2:**

- In
**Cell C5**we set which type of value we want.

**Step 3:**

- Now, run the put the below code on the command module.

```
Sub AutoFill7()
Dim source As Range, target As Range
Set source = Range("C5")
Set target = Range("C5:C11")
source.Autofill Destination:=target, Type:=xlFlashFill
End Sub
```

**Step 4:**

- Press
**F5**to run the code.

Here, we get only the numeric values by applying the autofill operation in Excel **VBA**.

**Read more: How to AutoFill Sequential Letters in Excel**

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we showed the autofill method of the range class failed problem and their solutions. I hope this will satisfy your needs.