How to determine number of records in a column - Excel 3

[jrh1]

I need to loop through a series of columns in Excel and bring back the number of records in each column.

I have tried using the following code -
Range("A6&quot.CurrentRegion.Rows.Count

The problem is that this will bring back the row count for the column that has the most rows in the current region, not the current column.

Any help would be greatly appreciated.

Thanks in advance,
Jeff

 

[Loomah]

Hi
This is one way of doing it. Note that this routine returns the last row containing data in each column. This includes blank columns - that is if you have an empty column this routie will return the last row as 1. If this isn't acceptable then it can be worked around.

Also this will only return a true number of records if your first record is in row 1. Subtract 1 (or more) from lRow to get a truer result.

Sub mit()
Dim iCol As Integer
Dim iCnt As Integer
Dim lRow As Long
'get last used column
iCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

For iCnt = 1 To iCol
    lRow = Cells(65536, iCnt).End(xlUp).Row
    MsgBox "Column " & iCnt & "'s last row is " & lRow
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[waynerenaud]

I played with Loomah's code (He should get the credit) and added a bit to allow for a blank column. Hope it helps.

Sub mit()
Dim iCol As Integer
Dim iCnt As Integer
Dim lRow As Long
'get last used column
iCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

For iCnt = 1 To iCol
lRow = Cells(65536, iCnt).End(xlUp).Row
If lRow = 1 And Cells(lRow, iCnt).Value = "" Then
MsgBox "Column " & iCnt & " has no data"
GoTo 1
End If
MsgBox "Column " & iCnt & "'s last row is " & lRow
1
Next
End Sub