Find Last row for Autofill macro

[iricketts]

I pull a report that grades answers for customers and the number of customers changes every day. I currently have the macro run to row 5000, but I want it to stop on the last row.

It has been a long time since my excel class and i'm in over my head. Sorry if my code looks *** backwards.

'Insert the grading formula and convert to hardcoded text
Dim LastRowColX As Integer
LastRowColX = Range("X65536").End(xlUp).Row
Range("Z3").Select
ActiveCell.Formula = "=SUM(IF(H3=$H$2,1,0),IF(I3=$I$2,1,0),IF(J3=$J$2,1,0),IF(K3=$K$2,1,0),IF(L3=$L$2,1,0),IF(M3=$M$2,1,0),IF(N3=$N$2,1,0),IF(O3=$O$2,1,0),IF(P3=$P$2,1,0),IF(Q3=$Q$2,1,0),IF(R3=$R$2,1,0),IF(S3=$S$2,1,0),IF(T3=$T$2,1,0),IF(U3=$U$2,1,0),IF(V3=$V$2,1,0),IF(W3=$W$2,1,0),IF(X3=$X$2,1,0),IF(Y3=$Y$2,1,0))/COUNTA($H$2:$Y$2)"
Range("Z3").Select
Range("Z3").AutoFill Destination:=Range("Z3:Z" & LastRowColX)
End Sub

Any help is appreciated.

Thanks.

 

[Smitty]

Welcome to the Board!

Using Cells(Rows.Count... will take any version of Excel into account:

    'Insert the grading formula and convert to hardcoded text
     Dim LastRowColX As Integer
     LastRowColX = Cells(Rows.Count, "X").End(xlUp).Row
     
     With Range("Z3")
        .Formula = "=SUM(IF(H3=$H$2,1,0),IF(I3=$I$2,1,0),IF(J3=$J$2,1,0),IF(K3=$K$2,1,0),IF(L3=$L$2,1,0),IF(M3=$M$2,1,0),IF(N3=$N$2,1,0),IF( O3=$O$2,1,0),IF(P3=$P$2,1,0),IF(Q3=$Q$2,1,0),IF(R3=$R$2,1,0),IF(S3=$S$2,1,0),IF(T3=$T$2,1,0),IF(U3=$U$2,1,0),IF(V3=$V$2, 1,0),IF(W3=$W$2,1,0),IF(X3=$X$2,1,0),IF(Y3=$Y$2,1,0))/COUNTA($H$2:$Y$2)"
        .AutoFill Destination:=Range("Z3:Z" & LastRowColX)
    End With

HTH,