Excel 2003

What approach in VBA to get the maximum number of rows of actual data in any one column.
I do know how to get Rows & Columns in the Used Range - but I need cells with data not cells with
formatting.


My mind does not work well in the 3rd dimension - i.e. arrays.

The consepts which come to mind are MAX() and Array() and .End()

Can anyone help?


Thanks!

EagleOne

Here is sample code getting the last row with data two ways: once specifying
the column number, and once specifying the column letter.

Sub AAAAA()
Dim LastRow As Long
'With column as number.
LastRow& = Cells(Rows.Count, 3).End(xlUp).Row
MsgBox LastRow&
'With column as letter'
LastRow& = Range("C" & Rows.Count).End(xlUp).Row
MsgBox LastRow&
End Sub

Hope this helps,

Hutch

"donoteventry" <donoteventry, "removes"" wrote:

Excel 2003

What approach in VBA to get the maximum number of rows of actual data in any one column.
I do know how to get Rows & Columns in the Used Range - but I need cells with data not cells with
formatting.


My mind does not work well in the 3rd dimension - i.e. arrays.

The consepts which come to mind are MAX() and Array() and .End()

Can anyone help?


Thanks!

EagleOne

Tom,

Thanks for the reply. Your approach manually does one column at a time.

Is there not a way to obtain the Max Rows in any column?

EagleOne

Tom Hutchins wrote:

Here is sample code getting the last row with data two ways: once specifying
the column number, and once specifying the column letter.

Sub AAAAA()
Dim LastRow As Long
'With column as number.
LastRow& = Cells(Rows.Count, 3).End(xlUp).Row
MsgBox LastRow&
'With column as letter'
LastRow& = Range("C" & Rows.Count).End(xlUp).Row
MsgBox LastRow&
End Sub

Hope this helps,

Hutch

"donoteventry" <donoteventry, "removes"" wrote:

Excel 2003

What approach in VBA to get the maximum number of rows of actual data in any one column.
I do know how to get Rows & Columns in the Used Range - but I need cells with data not cells with
formatting.


My mind does not work well in the 3rd dimension - i.e. arrays.

The consepts which come to mind are MAX() and Array() and .End()

Can anyone help?


Thanks!

EagleOne

Is there not a way to obtain the Max Rows in any column?

One idea...

Sub LastRow()
Dim LastRow As Long
LastRow = Cells.Find( _
What:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


<donoteventry; <removes; wrote in message
...
Tom,

Thanks for the reply. Your approach manually does one column at a time.

Is there not a way to obtain the Max Rows in any column?

EagleOne

Tom Hutchins wrote:

Here is sample code getting the last row with data two ways: once
specifying
the column number, and once specifying the column letter.

Sub AAAAA()
Dim LastRow As Long
'With column as number.
LastRow& = Cells(Rows.Count, 3).End(xlUp).Row
MsgBox LastRow&
'With column as letter'
LastRow& = Range("C" & Rows.Count).End(xlUp).Row
MsgBox LastRow&
End Sub

Hope this helps,

Hutch

"donoteventry" <donoteventry, "removes"" wrote:

Excel 2003

What approach in VBA to get the maximum number of rows of actual data in
any one column.
I do know how to get Rows & Columns in the Used Range - but I need cells
with data not cells with
formatting.


My mind does not work well in the 3rd dimension - i.e. arrays.

The consepts which come to mind are MAX() and Array() and .End()

Can anyone help?


Thanks!

EagleOne

By golly he hits a home run! Thanks!


"Dana DeLouis" wrote:

Is there not a way to obtain the Max Rows in any column?

One idea...

Sub LastRow()
Dim LastRow As Long
LastRow = Cells.Find( _
What:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Sub