MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help counting Rows, then using counter in Range

Posted by Dominic on December 29, 2001 2:01 AM

I need to know how to count the number of rows in a specific column and store that as a varaiable. Then I need to use that variable in a Range statement so I can select that group and make modifications. Also if there is a specific Reference I need to add to the macro to make this statement work. I tried using:
num1 = CountA(A:A)
but I got a "Compile error: Sub or Function not defined" on the CountA statement. Even if this is the correct syntax I still need to know how to use it like:
Range ("A:Anum1").Select
and I'm quite sure that later statement is wrong. Please help.

Posted by Tom Urtis on December 29, 2001 2:56 AM

To return a COUNTA function for column A:
ActiveCell.FormulaR1C1 = "=COUNTA(C1)"
Be sure your active cell is not in column A,
to avoid a circular reference.

To select all populated cells in column A,
if they are contiguously populated, starting
in A1, you can use:
Range("A1", Range("A1").End(xlDown)).Select

Or if they are noncontiguous, you can use:
Range("A1", Range("A65536").End(xlUp)).Select

Tom Urtis