Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display count of rows | Excel Worksheet Functions | |||
Getting Count field to recognise rows with negative values in Exc. | Excel Worksheet Functions | |||
Count Rows with Conditional Format? | Excel Discussion (Misc queries) | |||
Reduce columns and rows count? | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |