Drag formulas from Range with several variables, Error 1004

Question

I have a task to drag formulas in several columns based on length of one specific column (which length can vary).

I managed to do it, but had to create new line of code for each range.
Sample of my Variant 1:

Sub DragRows()
        
    Dim LastRowEPS As Integer
    Dim tr As Range
    
    Set tr = Range("A14:G" & LastRowEPS)
    
    Range("A14:G14").Select
    Selection.AutoFill Destination:=tr, Type:=xlFillDefault
    
    Set tr = Range("I14:K" & LastRowEPS)
    
    Range("I14:K14").Select
    Selection.AutoFill Destination:=tr, Type:=xlFillDefault
    
End Sub

I want to optimize my code and include several variable ranges in one line of code.
Here is my Variant 2:

Sub DragRows()
    
    Dim LastRowEPS As Integer
    Dim tr As Range
    
    LastRowEPS = Sheet1.Cells(14, 12).End(xlDown).Row
    
    Set tr = Range("A14:G" & LastRowEPS & ", I14:K" & LastRowEPS & ", M14:N" & LastRowEPS & ", P14:P" & LastRowEPS)
    
    Range("A14:G14,I14:K14,M14:N14,P14:P14").Select
    Selection.AutoFill Destination:=tr, Type:=xlFillDefault
    
End Sub

The selection process works, and tr.Range is defined properly, but VBA autofill shows Error:

Run-time error '1004': AutoFill method of Range class failed

Is it possible to include several variable ranges as destination of autofill or any other way to optimize my code?

Answer 1

Actually i found out a solution, which i was lookin for. Maybe for someone it will be helpful. .AutoFill is bad with multiple ranges, that's why simple use of .FillDown is an answer here:

Sub DragRows()

Dim LastRowEPS As Integer
Dim tr As Range

LastRowEPS = Sheet1.Cells(14, 12).End(xlDown).Row

Set tr = Sheet1.Range("A14:G" & LastRowEPS & ", I14:K" & LastRowEPS & ", M14:N" & LastRowEPS & ", P14:P" & LastRowEPS)
tr.FillDown

End Sub

Answer 2

AutoFill, Formula, FillDown

• The following shows two different approaches.
• OP already revealed the superior solution, which is covered in the second procedure.

Three Solutions (Inferior: Applied to Four Ranges)

Sub dragRows()
    ' Define constants.
    Const FirstRow As Long = 14
    Const LastRowCol As Long = 12
    Dim Cols As Variant
    Cols = Array("A:G", "I:K", "M:N", "P")
    ' In worksheet...
    With Sheet1
        ' Determine Rows Count.
        Dim RowsCount As Long
        RowsCount = .Cells(FirstRow, LastRowCol).End(xlDown).Row - FirstRow + 1
        ' Declare variables
        Dim rng As Range
        Dim n As Long
        ' Define and fill each range.
        For n = LBound(Cols) To UBound(Cols)
            Set rng = .Columns(Cols(n)).Rows(FirstRow)
            ' Choose one of the following solutions
            rng.AutoFill Destination:=rng.Resize(RowsCount), Type:=xlFillDefault
            'rng.Resize(RowsCount).Formula = rng.Formula
            'rng.Resize(RowsCount).FillDown
        Next n
    End With
End Sub

FillDown Solution (Superior: Applied to One Range)

Sub dragRowsFillDown()
    ' Define constants.
    Const FirstRow As Long = 14
    Const LastRowCol As Long = 12
    Dim Cols As Variant
    Cols = Array("A:G", "I:K", "M:N", "P")
    ' In worksheet...
    With Sheet1
        ' Determine Rows Count.
        Dim RowsCount As Long
        RowsCount = .Cells(FirstRow, LastRowCol).End(xlDown).Row - FirstRow + 1
        ' Declare variables
        Dim rng As Range
        Dim n As Long
        ' Define (non-contiguous) range.
        For n = LBound(Cols) To UBound(Cols)
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Columns(Cols(n)).Rows(FirstRow) _
                                                      .Resize(RowsCount))
            Else
                Set rng = .Columns(Cols(n)).Rows(FirstRow).Resize(RowsCount)
            End If
        Next n
    End With
    rng.FillDown
End Sub