Hello!

I would like to find the last empty row of a spreadsheet
so I can place static information at the end for EOF
processing. The spread sheet is always changing the
number of rows so I would need to do this dynamically in
my macro. I have no problem doing it statically.

Thank you
Mike

set rng = rng.SpecialCells(xlLast)

although this can overstate what you might think is the last empty row

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

==< end of John's post ===


------------------
so you could modify the above to be a function

Public Function GetRealLastCell(sh As Worksheet)
Dim RealLastRow As Long
Dim RealLastColumn As Long
With sh
On Error Resume Next
RealLastRow = _
Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", sh.Range("A1"), , , xlByColumns, xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End With
End Function



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

"Mike Potocki" wrote in message
...
Hello!

I would like to find the last empty row of a spreadsheet
so I can place static information at the end for EOF
processing. The spread sheet is always changing the
number of rows so I would need to do this dynamically in
my macro. I have no problem doing it statically.

Thank you
Mike

You can reset Excel's cell counting so that the SpecialCells method
gives you the correct last cell.
I use:
x = ActiveSheet.UsedRange.Rows.Count
y = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Cells.SpecialCells(xlLastCell).Select
Steve

"Tom Ogilvy" wrote in message ...
set rng = rng.SpecialCells(xlLast)

although this can overstate what you might think is the last empty row

How does your code know when it needs to be reset?

--
Regards,
Tom Ogilvy


"Steve Kite" wrote in message
om...
You can reset Excel's cell counting so that the SpecialCells method
gives you the correct last cell.
I use:
x = ActiveSheet.UsedRange.Rows.Count
y = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Cells.SpecialCells(xlLastCell).Select
Steve

"Tom Ogilvy" wrote in message
...
set rng = rng.SpecialCells(xlLast)

although this can overstate what you might think is the last empty row