how to count populated rows?
 

How do I find out how many rows have data in them starting with row 4 and
beyond? It's ok if I just know that column A has data in it.

I'm using VBA. I don't know how to use functions very well. But I know
visual basic some. So if you could give me some VBA code to do this, that
would be great. Thanks.

Ryan,

I was doubtful that just knowing there was data would be enough...
So following is some code to cover several possibilities.
(you can understand how some of this works if you look up the terms
used in the help file - just stick the cursor in a word and press F1)
'------------------
Sub FindStuff()
Dim lngRw As Long
Dim lngCount As Long
Dim strBlanks As String
Dim strData As String

'Last row with data in Column A
lngRw = Cells(Rows.Count, 1).End(xlUp).Row

'Count of cells with data in Column A (below cell A4)
lngCount = WorksheetFunction.CountA(Range("A5", Cells(Rows.Count, 1)))

'Address of cells with blanks (below cell A4)
strBlanks = Range("A5", _
Cells(lngRw, 1)).SpecialCells(xlCellTypeBlanks).Address

'Address of cells with values (below cell A4)
strData = Range("A5", _
Cells(lngRw, 1)).SpecialCells(xlCellTypeConstants).Address

'Address of cells with formulas (below cell A4)
strData = strData & "," & Range("A5", _
Cells(lngRw, 1)).SpecialCells(xlCellTypeFormulas).Address

'Put it all together and display it
MsgBox "Last row is " & lngRw & vbCr & "Location of blank cells is " & _
strBlanks & vbCr & "Total cell count with data is " & _
lngCount & vbCr & "Location of cells with data is " & strData
End Sub
'-------------------------------

Regards,
Jim Cone
San Francisco, USA



"Ryan Cain" wrote in message
...
How do I find out how many rows have data in them starting with row 4 and
beyond? It's ok if I just know that column A has data in it.

I'm using VBA. I don't know how to use functions very well. But I know
visual basic some. So if you could give me some VBA code to do this, that
would be great. Thanks.

to do it using a worksheet function try this:

=COUNTIF(A4:A100,"""")


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=394812

If your data are contiguous, COUNTA(A:A) will give you the result. If
not, Activesheet.Cells(ActiveSheet.rows.count,1).end(xl up).row will
give you the last row with data. Adjust the result for the offset for
A4.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
How do I find out how many rows have data in them starting with row 4 and
beyond? It's ok if I just know that column A has data in it.

I'm using VBA. I don't know how to use functions very well. But I know
visual basic some. So if you could give me some VBA code to do this, that
would be great. Thanks.

My previous post was incorrect about the limitation on when COUNTA
would be applicable. It will work with contiguous *and* non-contiguous
data.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
How do I find out how many rows have data in them starting with row 4 and
beyond? It's ok if I just know that column A has data in it.

I'm using VBA. I don't know how to use functions very well. But I know
visual basic some. So if you could give me some VBA code to do this, that
would be great. Thanks.