Excel VBA: Autofill Method of Range Class Failed (3 Solutions) -

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

What is Autofill Method in Excel VBA?

How to Apply AutoFill Method in VBA?

AutoFill Method of Range Class Failed: 3 Cases with Fixes

1. Autofill Method of Range Class Failed Vertically and Horizontally

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

3. Solved AutoFill Range Class Failed Problem with Desired Cells

Some Examples of AutoFill Method in Excel VBA

1. AutoFill Same Format

2. AutoFill Method with Desired Option

Download Practice Workbook

Conclusion

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.

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.

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.

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:

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.

Similar Readings:

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.

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:

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.

Download Practice Workbook

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

Autofill Method of Range Class Failed.xlsm

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.

Excel VBA: Autofill Method of Range Class Failed (3 Solutions)

What is Autofill Method in Excel VBA?

How to Apply AutoFill Method in VBA?

AutoFill Method of Range Class Failed: 3 Cases with Fixes

1. Autofill Method of Range Class Failed Vertically and Horizontally

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

3. Solved AutoFill Range Class Failed Problem with Desired Cells

Some Examples of AutoFill Method in Excel VBA

1. AutoFill Same Format

2. AutoFill Method with Desired Option

Download Practice Workbook

Conclusion

Further Readings

Leave a reply Cancel reply