MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Beginner VBA help - Please help quickly


Posted by spock on December 02, 2001 6:11 PM

In VBA: Need to count how many rows of cells are in Column A - from Cell A2, until the last non-null cell.

Basically I need the equivalent of the worksheet function

=COUNTIF(A2:A65536,"<>""")

but in VBA


Posted by Ivan F Moala on December 02, 2001 6:31 PM

Application.WorksheetFunction.CountIf(Range("A3:A120"), """")

Posted by Ivan F Moala on December 02, 2001 6:32 PM

Re: Application.WorksheetFunction.CountIf(Range(


Posted by Ivan F Moala on December 02, 2001 6:33 PM

last bit should be """"


Posted by Ivan F Moala on December 02, 2001 6:35 PM

still didn't come out right

should be Application.WorksheetFunction.CountIf(Range("A3:A120"), ">0")

Ivan

Posted by spock on December 02, 2001 6:39 PM

Thanks Ivan!

Posted by Juan Pablo G. on December 02, 2001 6:42 PM

Try with Application.Counta(Range("A:A"))

Posted by Bariloche on December 02, 2001 6:49 PM

If you want to count zeros

spock,

If you want to count zeros, use the CountA function, i.e., CountStuff = Application.WorkbookFunction.CountA(Range("A1:A65536").


have fun

Posted by Bariloche on December 02, 2001 6:51 PM

oops, left out a closing parentheses (NT)