Michael,

If you want to fill as far down as column A is currently filled:

Dim myRow As Long
myRow = Range("A65536").End(xlUp)(2).Row

Range("K10").FormulaArray = "=SUM(COUNTIF(RC1:RC10,R1C[-10]:R7C[-10]))"
Range("K10").AutoFill Destination:=Range("K10:N10"), Type:=xlFillDefault
Range("K10:N10").AutoFill Destination:=Range("K10:N" & myRow),
Type:=xlFillDefault

HTH,
Bernie
MS Excel MVP

"Michael168 " wrote in message
...
Hello,VBA Experts here

I need a vba code to run from row 10 until the last row in a worksheet.
The last row is not fix.

Presntly I am using the array formula as below for counting.

Cell K10 : {=SUM(COUNTIF($A10:$J10,$A$1:$A$7))}

Cell L10 : {=SUM(COUNTIF($A10:$J10,$B$1:$B$7))}

Cell M10 : {=SUM(COUNTIF($A10:$J10,$C$1:$C$7))}

Cell N10 : {=SUM(COUNTIF($A10:$J10,$D$1:$D$7))}

Thanks,
Michael.


---
Message posted from http://www.ExcelForum.com/