Counting number of rows (with skips)

[MasterMonk]

Hi

I have a large sheet, "Sheet1" that is dynamic. I want to count the number of rows, however there are sometimes single empty rows in between my rows. Is there an elegant way to count the number of rows in the sheet?

NumberofRows = Worksheets("Sheet1").Range("A1").End(xlDown).Row

I've used the above but it will count until the break in the worksheet and not continue. Help please!

[VBA Noob]

How about

NumRows = Application.WorksheetFunction.CountA(Range("A:A"))

VBA Noob

[MasterMonk]

How about

NumRows = Application.WorksheetFunction.CountA(Range("A:A"))

VBA Noob

That works, but that means I will search EVERY cell right? A little inefficient? My next job in the line is a:

 Do While x < NumRows
"execute x command"
loop

If I just select ALL rows and I run a bunch of these, it will bog down the system. Are there any other suggestions?

[royUK]

My code counts the blanks in ColumnA used cells.

It wouldn't hurt to thank helpers for their assistance!

[MasterMonk]

Oh really!

That's great, thanks a lot, I will try that out.

[royUK]

Like this

Dim rng As Range
Dim NumRows As Long

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

NumRows = Application.WorksheetFunction.CountA(rng)