VBA Sub that applies a formula to a block of cells, except left-most column and header row

Question

I'm trying to apply a formula to a block of cells, like the title says.

Ultimately, I'd like the formula to only be applied to cells that have values in row 1 and column A.

I tried this, but quickly realized I was just applying the formula to every cell in the sheet except the left-most and header row.

Sub A_B4()
Application.CutCopyMode = False
Range("B2").Select
ActiveCell.Formula = "=RC1&"" ""&R1C"
Selection.AutoFill Destination:=Range(Range("B2"), Range("B2").End(xlDown).End(xlToRight))


End Sub

I think I need to use something along the line of this:

With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))

Where it would count the amount of rows that have data in column A (except the header which that piece does not do), and apply the formula. Then count the amount of columns that have data in row 1, and apply the formula down. But this is where I'm lost. I feel like there should be an easier way rather than counting the columns in row 1 that contain data, converting that to a letter, then applying the formula.

Scott Craner Scott Craner · Accepted Answer · 2017-11-01T21:54:17

With R1C1 formula notation there is no need for autofill.

Sub A_B4()
    Dim lr As Long
    Dim lc As Long

    With ActiveSheet
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range("B2", .Cells(lr, lc)).FormulaR1C1 = "=RC1&"" ""&R1C"
    End With
End Sub

VBA Sub that applies a formula to a block of cells, except left-most column and header row

1 Answers