Excel vba to autofill formulae from a column to last column in excel

Question

I do have a macro which adds formula in N2 cell and autofill it till last row in sheet.

Sub Formulae_to_find_EachFeatureCount()
    Dim LastRow As Long
    LastRow = Range("G" & Rows.Count).End(xlUp).Row

    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIFS(C2,R1C,C1,RC7)"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N" & LastRow), Type:=xlFillDefault
   ' Range("N2:N10").Select
End Sub

I could also find last column (column name and number) with below code:

    Dim LastColumn As Integer
     'Search for any entry, by searching backwards by Columns.
    LastColumn = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    LastCol = Replace(Cells(1, LastColumn).Address(False, False), "1", "")

Now I want to autofill formulae from N column to last (used) column.

loop through the columns and fill the 1st cell and then in the end use autofill in one go — Siddharth Rout

Mrig Mrig · Accepted Answer · 2017-07-03T10:30:23

I guess this is what you are looking for:

Sub Formulae_to_find_EachFeatureCount()
    Dim LastRow As Long
    LastRow = Range("G" & Rows.Count).End(xlUp).Row

    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIFS(C2,R1C,C1,RC7)"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N" & LastRow), Type:=xlFillDefault
    Range("N1:N" & LastRow).Select
    Selection.AutoFill Destination:=Range("N2:" & lastcol & LastRow), Type:=xlFillDefault
End Sub

NOTE: However using SELECT should be avoided.

Excel vba to autofill formulae from a column to last column in excel

1 Answers