I put together a simple routine that will find the last Row and last Column w/ data in a spreadsheet
Without the pitfalls and limitations of
End(xlup), End(xlDown) and "simple" UsedRange
Any critique is welcomed

Sub LstRow_LstColumn(
Dim Rw As Range, Clm As Rang
Dim x As Single, LRw As Single, LClm As Singl

If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 Then Exit Su
With ActiveSheet.UsedRange
D
x = x +
Set Rw = .Offset(.Rows.Count - x).Resize(1
Loop Until WorksheetFunction.CountA(Rw) <
LRw = Rw.Ro
x =
D
x = x +
Set Clm = .Offset(0, .Columns.Count - x).Resize(, 1
Loop Until WorksheetFunction.CountA(Clm) <
LClm = Clm.Colum
End Wit
End Sub

chris a écrit :
I put together a simple routine that will find the last Row and last Column w/ data in a spreadsheet.
Without the pitfalls and limitations of:
End(xlup), End(xlDown) and "simple" UsedRange.
Any critique is welcomed.

Sub LstRow_LstColumn()
Dim Rw As Range, Clm As Range
Dim x As Single, LRw As Single, LClm As Single

If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 Then Exit Sub
With ActiveSheet.UsedRange
Do
x = x + 1
Set Rw = .Offset(.Rows.Count - x).Resize(1)
Loop Until WorksheetFunction.CountA(Rw) < 0
LRw = Rw.Row
x = 0
Do
x = x + 1
Set Clm = .Offset(0, .Columns.Count - x).Resize(, 1)
Loop Until WorksheetFunction.CountA(Clm) < 0
LClm = Clm.Column
End With
End Sub


Hi Chris,

I use these to find out :
* last row
Lx = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

* last column
Cx = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

The very last cell as address (sheet starting in A1) :
MsgBox Cells(Lx, Cx).Address

HTH
@+
FxM

"FxM" wrote in message
...

I use these to find out :

Lx = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row


Apart from the nasty [A1], which can be circumvented by Range("A1"), I like
that. It doesn't however improve IMO upon

Cells(Rows.Count,"A").End(xlUp).Row

Hi Bob

Not sure that the final goal is the same.
Consider one sheet with only range("G5") filled in :

Sub test()
k = Cells(Rows.Count, "A").End(xlUp).Row
Lx = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
End Sub

returns
k = 1 (because nothing in column A)
Lx = 5 ('lowest' row of any column)

@+
FxM

When writing a program to Reset the UsedRange, I have tried everything known
to man under the sun. From lots of testing, I have found that using "Find"
as others have mentioned is the fastest way to do it.
Keep in mind that a Cell Note may contain useful information that is
"Outside" this "last cell" if using this to deleting anything.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Bob Phillips" wrote in message
...
"FxM" wrote in message
...

I use these to find out :

Lx = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row


Apart from the nasty [A1], which can be circumvented by Range("A1"), I
like
that. It doesn't however improve IMO upon

Cells(Rows.Count,"A").End(xlUp).Row

Hi

You can use find
See the functions I use in this example
http://www.rondebruin.nl/copy1.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"chris" wrote in message ...
I put together a simple routine that will find the last Row and last Column w/ data in a spreadsheet.
Without the pitfalls and limitations of:
End(xlup), End(xlDown) and "simple" UsedRange.
Any critique is welcomed.

Sub LstRow_LstColumn()
Dim Rw As Range, Clm As Range
Dim x As Single, LRw As Single, LClm As Single

If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 Then Exit Sub
With ActiveSheet.UsedRange
Do
x = x + 1
Set Rw = .Offset(.Rows.Count - x).Resize(1)
Loop Until WorksheetFunction.CountA(Rw) < 0
LRw = Rw.Row
x = 0
Do
x = x + 1
Set Clm = .Offset(0, .Columns.Count - x).Resize(, 1)
Loop Until WorksheetFunction.CountA(Clm) < 0
LClm = Clm.Column
End With
End Sub

and the limitations of End(xlUP) and End(xlDown) are ...?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"chris" wrote in message
...
I put together a simple routine that will find the last Row and last
Column w/ data in a spreadsheet.
Without the pitfalls and limitations of:
End(xlup), End(xlDown) and "simple" UsedRange.
Any critique is welcomed.

Sub LstRow_LstColumn()
Dim Rw As Range, Clm As Range
Dim x As Single, LRw As Single, LClm As Single

If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 Then Exit Sub
With ActiveSheet.UsedRange
Do
x = x + 1
Set Rw = .Offset(.Rows.Count - x).Resize(1)
Loop Until WorksheetFunction.CountA(Rw) < 0
LRw = Rw.Row
x = 0
Do
x = x + 1
Set Clm = .Offset(0, .Columns.Count - x).Resize(, 1)
Loop Until WorksheetFunction.CountA(Clm) < 0
LClm = Clm.Column
End With
End Sub

In article ,
says...
and the limitations of End(xlUP) and End(xlDown) are ...?


Many, Bob. Depending on your intent, the results may surprise you.
Even for finding the last cell with data in a column, say, i,
Cells(rows.count,i).end(xlup) is flawed!

Try the End() properties (methods?) with
a completely empty column
a completely full column,
a partially filled column with data starting at the top,
a partially filled column with data starting at the bottom, and
a partially filled column with empty cells at the top and the bottom,
a partially filled column with multiple blocks of data separated by
empty cells.

And, in the context of this discussion, as a tool for finding the last
used cell, which row/column would one check?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions