In Excel VBA, how can I identify last occupied cell in a column?

[Guest]

I used to use Range("A65536").End(xlUp)).Count
but it seems silly with over a million rows (Excel 2007).

 

[Guest]

Excel isn't looping up the worksheet I wouldn't think. My guess is it knows
where things are. I wouldn't be concerned that it would be much slower.


If Val(Application.Version) <12 Then
'not in Excel 2007
LastUsedRow = cells(Rows.Count,1).End(xlup).row
Else
'in Excel 2007 (or later)
LastUsedRow = cells(Rows.CountLarge,1).End(xlup).row
End If

I would guess - although I don't have xl2007 readily available to test it.
Note the use of CountLarge.

 

[Helmut Weber]

Hi Rick,

With ActiveSheet
MsgBox .Cells(Rows.Count, 1).End(xlUp).Row
End With

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Helmut Weber]

Hi Tom,

CountLarge

I'll be on 2007 soon.

Thx.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Harlan Grove]

I used to use Range("A65536").End(xlUp)).Count
but it seems silly with over a million rows (Excel 2007).

Well the last used cell in a column would always be within the
worksheet's UsedRange. If Excel's object model automatically moves up
to the UsedRange before actually checking cell contents, no big deal
in XL2007.