How to find the last ROW with any data.

[Sharlene England]

What would be the syntax in my macro for getting the last row with data?

am trying
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
towhere = ActiveCell.Row

But this is 3231 or something, only want if there is data.

Thanks.

--
Sharlene England
Network Administrator
Westwood Companies
Vernon, BC, Canada

[J.E. McGimpsey]

If you know that all rows with data have data in column A, say, then
this is one way:

Dim toWhere As Long
toWhere = Range("A" & Rows.Count).End(xlUp).Row

of course, you can substitute any other column, if applicable.

In article <uDlBASfhCHA.2412@tkmsftngp12>, Sharlene England

[Patrick Molloy]

almost:=

towhere = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

or, if you want the last row of a specific column

towhere = ActiveSheet.Range("A65536").End(xlup).row

Patrick Molloy
Microsoft Excel MVP

"Sharlene England" <seng...@westwoodcompanies.com> wrote in message
news:uDlBASfhCHA.2412@tkmsftngp12...

[Andrew Ollivier]

Patrick & J E McGimpsey (and other experts of course)

Whenever the question is asked about 'last' ranges there seem to be two
different answers given - both of which appear to work.
1) .Range("A" & Rows.Count).End(xlUp).Row
2) .Range("A65536").End(xlup).row

My question is why quote the second form which creates a specific dependency
on implementation "knowledge". The first form appears to do the job in a HW
implementation independent way. Is there a performance issue or specific
risk with form 1 (e.g. is the second form used to protect from when the
value of ROWS may be wrong)?

Andrew Ollivier

"Patrick Molloy" <patrick...@hotmail.com> wrote in message
news:uBIkvwfhCHA.2508@tkmsftngp08...

[Dick Kusleika]

Andrew

I'll speak for the non-experts. I use version 2 because I'm inherently
lazy. I think some people use version 1 in case Microsoft decides to
increase the number of rows in excel. Of course if they decide to decrease
the number of rows, only version 1 will work. I'm sure there's no
performance difference.

Think of all the mainframe programmers who wrote programs with only a
two-digit date year. In the late '90s they were rolling in the dough. So
the moral is, if you want your code to be bullet proof, use version 1. But
if want your customers to suddenly need your services again at some
unspecified time in the future...well, you know.

--
Dick Kusleika
MVP - Excel

"Andrew Ollivier" <olli...@paradise.net.nz> wrote in message
news:u3dz#GghCHA.1736@tkmsftngp11...

[Tom Ogilvy]

There is a third way although not functionally different.

set rng = Cells(rows.count,1).End(xlup)

String concatenation takes time and isn't necessary.

Getting the row seems indirect to me. If you later need the row, rng.row
will do.

from rng you can address in many other ways rng.EntireRow, rng(2), and so
forth

Using rows.count is what you say. There is no reason not to use it that I
am aware of.

But it looks to me like the original poster is dealing with a usedrange that
hasn't been reset and is getting an overstated last used row. Here is a
more robust way to find the actual last cell with data:

In a thread subject Re: Change What Excel Believes Is The Last Cell
In Microsoft.Public.Excel.Programming on October 15, 1999

This was posted by John Green
"The following code will find the last row and last column that contain data
on the active worksheet:"

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub

This is a version posted by David Chazin with some additional error checking

Sub LastCell_Select()
'David Chazin 05-Mar-1999
'Selects the cell at the bottom-right corner of the used area
' of the currently selected worksheet.
Range(LastCell_Get()).Select
End Sub

Function LastCell_Get() As String

'David Chazin -- September 1999.
'Returns the Address (e.g. "$AW$235") of the cell at the
' bottom-right corner of the used area of the currently
' selected worksheet. Returns "" if the worksheet is empty.
' Thanks to Dave Braden for the idea of trapping for an
' empty worksheet. (This is not the way he would implement
' it, but the idea itself is his).

On Error GoTo LastCell_Get_ErrorHandler

If Range("A1").SpecialCells(xlLastCell).Value <> "" Then
LastCell_Get = Range("A1").SpecialCells(xlLastCell).Address()
Else
LastCell_Get = _
Cells(Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , , _
xlByRows, xlPrevious).Row, _
Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , , _
xlByColumns, xlPrevious).Column).Address()
End If
Exit Function
LastCell_Get_ErrorHandler:

If Err.Number = 91 Then
'If the worksheet is empty....
LastCell_Get = ""
Else
Call MsgBox("Error #" & Err.Number & " was generated by " & _
Err.Source & ": " & Err.Description, vbOKOnly + vbExclamation, _
"LastCell_Get()", Err.HelpFile, Err.HelpContext)
End If

End Function

Regards,
Tom Ogilvy

Andrew Ollivier <olli...@paradise.net.nz> wrote in message
news:u3dz#GghCHA.1736@tkmsftngp11...

[Andrew Ollivier]

Thanks to all
I appreciated the responses - as always there are many ways to skin a cat -
it's what makes coding in Excel so interesting ;)
Andrew

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:#hA5FShhCHA.1652@tkmsftngp11...