Last Cell

[Dave Unger]

Hi everybody,

I'm relatively new to programming with VBA (Excel 97), but it
didn't take me long to discover that Excel had a problem with the
"last cell" at times. Browsing the Excel sites, including this
group, confirmed that this was a known issue, and offered several
workarounds.

One of them was to reset the last cell using the statement
"ActiveSheet.UsedRange" before
"ActiveSheet.Cells.SpecialCells(xlLastCell).Select". I find that
this works some of the time, but not if one of the cells outside the
data area is formatted differently from the others.

To date, the only thing that seems to work consistently for me is a
routine similar to the following (which I am NOT taking credit for,
it's a modification of code I found on the web).

Private Sub realLastCell()

Dim rCnt As Long
Dim cCnt As Integer

' What Excel thinks is the last cell
ActiveSheet.Cells.SpecialCells(xlLastCell).Select

rCnt = ActiveSheet.Cells.SpecialCells(xlLastCell).ROW
cCnt = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

' Determine the last row with containing data
Do While Application.CountA(ActiveSheet.ROWS(rCnt)) = 0 And
rCnt <> 1
rCnt = rCnt - 1
Loop

' Determine the last column containing data
Do While Application.CountA(ActiveSheet.Columns(cCnt)) = 0 And
cCnt <> 1
cCnt = cCnt - 1
Loop
Cells(rCnt, cCnt).Select
End Sub

Consequently, whenever it's critical that an application identifies
the last cell, I include code similar to the above, just to be sure.

I guess I would like to know if this is considered a bit "klunky",
or if experienced programmers would choose a similar method.

Thanks,

DaveU

 

[Tom Ogilvy]

If you want to find the "RealLastCell with data:" ;the intersection of the
last used row and last used column:


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


[ the cell selected is at the intersection of the last row and last column
and may be empty, so you can use each result independently]


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

 

[Tom Ogilvy]

by the way, except for a bug in xl2000 (later fixed), there is nothing wrong
with UsedRange or xlLastCell. the problem is definition. These return the
rectangular area for which excel is maintaining data on the cells. The
other cells are all "virtual". Excel tries to only store data that is
required (such as formatting as you pointed out) on a minimum rectangular
area. It doesn't mean there is data in the cells. Unfortunately, people
apply their own definition/interpretation to it and then cry foul when it
doesn't match.

 

[Dave Unger]

Hi Tom,

Appreciate the explanation of xlLastCell, I guess I was one of those
who mis-interpreted the definition. Also, because you include 2
samples of code in your 1st reply, I'm assuming the answer to my
question is "yes".

Thanks for your reply,

regards,

DaveU