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