VBA for each row COUNTA specific columns, put results in column HY

[LNG2013]

Hi Friendly Folks,

I need help with a VBA script that looks at each row on Sheet1, and does a CountA for Columns AI, AO, AU, BA, BG, CG, CM, CS, CY, DE, EE, EK, EQ, EW, FC, GC, GI, GO, GU, HA. It should put the put put in each row, in column HY.

Or Numerical equivalent
35, 41, 47, 53, 59, 85, 91, 97, 103, 109, 135, 141, 147, 153, 159, 185, 191, 197, 203, 209


Thank you warmly in advance!

 

[pbornemeier]

This should work:

It assumes data starts in row 2 and the last populated cell in column A is in the last populated row of the worksheet.

Sub BigCOUNTA()

    Dim lLastDataRow As Long
    
    lLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("HY2:HY" & lLastDataRow)
        .FormulaR1C1 = _
            "=COUNTA(RC35, RC41, RC47, RC53, RC59, RC85, RC91, RC97, RC103, RC109, RC135, RC141, RC147, RC153, RC159, RC185, RC191, RC197, RC203, RC209)"
        Application.Calculate
        .Value = .Value
    End With
End Sub