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, or formula from 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 formula.

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.

AutoFill Method in VBA


Step 2:

  • Named the macro AutoFill1.
  • Then, press OK.


Step 3:

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

AutoFill Method in VBA


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

AutoFill Method in VBA


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.


Step 7:

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

AutoFill Method in VBA


Step 8:

  • Again, press F5 to run the code.

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


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

Autofill Method of Range Class Failed Vertically and Horizontally

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

Autofill Method of Range Class Failed Vertically and Horizontally

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 top to bottom as per our desire.

Step 3:

  • Modify the previous code like below:

Autofill Method of Range Class Failed Vertically and Horizontally

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.

Autofill Method of Range Class Failed Vertically and Horizontally

Step 6:

  • Again, press F5 to run the code.

Autofill Method of Range Class Failed Vertically and Horizontally

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


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

AutoFill Method of Range Class Failed Horizontally and Vertically Simultaneously

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

AutoFill Method of Range Class Failed Horizontally and Vertically Simultaneously

Step 2:

  • Run the code by pressing the F5 button.

AutoFill Method of Range Class Failed Horizontally and Vertically Simultaneously

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


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 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

AutoFill Range Class Failed Problem with Desired Cells

Step 3:

  • Press the F5 button to run the code.

AutoFill Range Class Failed Problem with Desired Cells

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.

AutoFill Failed Same Format

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

AutoFill Failed Same Format

Step 3:

  • Now, press F5 to run the code.

Here, we auto-filled with the given format.


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.

AutoFill Method Failed with Desired Option

Step 1:

  • Create a new macro named AutoFill7.

Step 2:

  • In Cell C5 we set which type of value we want.

AutoFill Method Failed with Desired Option

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

AutoFill Method Failed with Desired Option

Step 4:

  • Press F5 to run the code.

AutoFill Method Failed with Desired Option

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.


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.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo