VBA to obtain Max Rows in any column in Used Reange
 

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

VBA to obtain Max Rows in any column in Used Reange
 

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

VBA to obtain Max Rows in any column in Used Reange
 

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

VBA to obtain Max Rows in any column in Used Reange
 

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

VBA to obtain Max Rows in any column in Used Reange
 

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