How to fill down functions in four columns to where the data in column A stops?

Question

I am trying to fill down functions in four columns to where the data in column A stops, but by the time it gets to the fourth FillDown, it takes a very long time.

Is there any way to rewrite it more efficiently?

Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"
Range(ActiveCell, ActiveCell.Offset(0, 0).End(xlDown)).FillDown

Range("E2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],7,2)"
Range(ActiveCell, ActiveCell.Offset(0, 0).End(xlDown)).FillDown

Range("Z2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-22]<>""SO"",RC[-22],RC[-12])"
Range(ActiveCell, ActiveCell.Offset(0, 0).End(xlDown)).FillDown

Range("AA2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(OR((RC[-22]=""17""),(RC[-22]<>""11""),(RC[-23]=""SO"")),""HQ"",""Remote"")"
Range(ActiveCell, ActiveCell.Offset(0, 0).End(xlDown)).FillDown

You can write a formula to a range in one go, without filling down. Also see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jun 16 '21 at 21:22
[Entering formula in one go](https://stackoverflow.com/questions/25621448/formula-autofill-down-multiple-columns-using-specific-cells-in-vba) — Siddharth Rout, Jun 16 '21 at 21:54

score 2 · Accepted Answer · answered Jun 16 '21 at 21:56

I see several issues here

You say ... to where the data in column A stops but are using .End(xlDown) on columns D, E, Z, AA. This may be filling the formula down to the botton of the sheet.
You don't need to Fill Down, simply apply the formula to a specified range
Select is not a needed

Consider this

Sub Demo()
    Dim ws As Worksheet
    Dim LastRow As Long
    
    Set ws = ActiveSheet ' or specify the required sheet
    
    With ws
        ' find where data in column A stops
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        If LastRow = 1 Then
            ' there is no data in column A
            Exit Sub
        End If
        
        .Range(.Cells(2, 4), .Cells(LastRow, 4)).FormulaR1C1 = "=LEFT(RC[-1],2)"
        .Range(.Cells(2, 5), .Cells(LastRow, 5)).FormulaR1C1 = "=MID(RC[-2],7,2)"
        .Range(.Cells(2, 26), .Cells(LastRow, 26)).FormulaR1C1 = "=IF(RC[-22]<>""SO"",RC[-22],RC[-12])"
        .Range(.Cells(2, 27), .Cells(LastRow, 27)).FormulaR1C1 = "=IF(OR((RC[-22]=""17""),(RC[-22]<>""11""),(RC[-23]=""SO"")),""HQ"",""Remote"")"
    End With
    
End Sub

score 0 · Answer 2 · answered Jun 16 '21 at 21:55

As @BigBen pointed out, you can avoid using .FillDown by just referencing the full range from the start. You can also avoid .Select at the same time by using functions like .Resize to reference larger ranges based on the current range.

Additionally, since your macro is writing formulas into cells, you can speed up the code by disabling the auto-calculation that happens whenever a cell is edited. It's also a good idea to disable any events that might be running from the Worksheet_Change procedure.

You can disable those two things using Application.Calculation = xlCalculationManual and Application.EnableEvents = False. After the code, you will want to return those two settings back to default with Application.Calculation = xlCalculationAutomatic and Application.EnableEvents = True

Here's an example of how to implement these suggestions:

    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    With Range("D2")
        .Resize(.End(xlDown).Row - .Row).FormulaR1C1 = "=LEFT(RC[-1],2)"
    End With
    
    With Range("E2")
    .Resize(.End(xlDown).Row - .Row).FormulaR1C1 = "=MID(RC[-2],7,2)"
    End With
    
    With Range("Z2")
    .Resize(.End(xlDown).Row - .Row).FormulaR1C1 = "=IF(RC[-22]<>""SO"",RC[-22],RC[-12])"
    End With
    
    With Range("AA2")
    .Resize(.End(xlDown).Row - .Row).FormulaR1C1 = _
        "=IF(OR((RC[-22]=""17""),(RC[-22]<>""11""),(RC[-23]=""SO"")),""HQ"",""Remote"")"
    End With
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

I preserved the .End(xlDown) method since I don't know what your sheet looks and that may be the best method to match your data format.

How to fill down functions in four columns to where the data in column A stops?

2 Answers2