UsedRange and LastCell feb08

[Neal Zimm]

Hi -
Am learning about some new to me worksheet properties.
I know the first example below is a count, but shouldn't the row numbers
be the same in both examples? Why are they not?
Thanks,
Neal Z.


debug.Print activesheet.usedrange.rows.count
5499

debug.Print activesheet.usedrange.specialcells(xlCellTypeLastCell).address
$CW$5498

 

[Dave Peterson]

If the usedrange starts in row 1, then the numbers will be the same.

with activesheet.usedrange
msgbox .row 'first row
msgbox .rows(.rows.count).row 'last row
msgbox .rows.count 'number of used rows
end with

 

[Jim Thomlinson]

Purely as an aside to your question both used range and last cell can at
times be wrong. Most of the time they are correct but there will be times
when they get messed up. I personally never use them for that reason (I like
to be right all of the time. My wife will attest to that). I use the
following function to return the last cell. It never fails...

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function

 

[Neal Zimm]

Thanks Dave,
So the UsedRange is "relative" and if I wanted the absolute last row#
there is more math to do, Yes?

 

[Neal Zimm]

Jim,
Thanks much.
To be on the lookout, can you give me a circumstance or two when the MSoft
supplied results are wrong?

Also, as a matter of good coding practices, do you always use so many
continuation lines, or is your code continued just for this community for
readability?

Thanks again,

 

[Neal Zimm]

Jim -
I took your code and tweaked it, below. It seems to test OK, to search
either a range or the worksheet. Any suggestions? Thanks much.

Public Function LastCellF(Optional ByVal ArgRng As Range, _
Optional ByVal Ws As Worksheet) As Range
'Output: Last Cell in argument range, or worksheet; as a range
' Input: Worksheet is checked if ArgRng is nothing.

Dim LasRow As Long
Dim LasCol As Integer

If ArgRng Is Nothing Then If Ws Is Nothing Then Set Ws = ActiveSheet

With Ws
If ArgRng Is Nothing Then
On Error Resume Next
LasRow = .Cells.Find(What:="*", After:=.Range("A1"),
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, MatchCase:=False).Row

On Error Resume Next
LasCol = .Cells.Find(What:="*", After:=.Range("A1"),
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, MatchCase:=False).Column

Else 'Have a range as input to be searched
On Error Resume Next
LasRow = ArgRng.Find(What:="*", After:=.Cells(ArgRng.Row,
ArgRng.Column), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, MatchCase:=False).Row

LasCol = ArgRng.Find(What:="*", After:=.Cells(ArgRng.Row,
ArgRng.Column), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, MatchCase:=False).Column
End If

On Error GoTo 0
If LasRow = 0 Then
LasRow = 1
LasCol = 1
End If

Set LastCellF = .Cells(LasRow, LasCol)

End With
End Function