Use:
Function LastRowInt(InWS As Worksheet) As Integer LastRowInt = InWS.UsedRange.Find(What:="*", After:=InWS.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row End Function Public Function DataRng(FirstCell As Range, WS As Worksheet) As Range 'Function to determine and return the true used range of the specified Worksheet Dim LastCell As Range, LastRow As Range, LastCol As Range Set LastRow = WS.Rows(WS.UsedRange.Find(What:="*", After:=FirstCell, Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row) Set LastCol = WS.Columns(WS.UsedRange.Find(What:="*", After:=FirstCell, Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column) Set LastCell = Application.Intersect(LastRow, LastCol) Set DataRng = WS.Range(FirstCell, LastCell) End Function
Function LastRowInt(InWS As Worksheet) As Integer LastRowInt = InWS.UsedRange.Find(What:="*", After:=InWS.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row End Function Public Function DataRng(FirstCell As Range, WS As Worksheet) As Range 'Function to determine and return the true used range of the specified Worksheet Dim LastCell As Range, LastRow As Range, LastCol As Range Set LastRow = WS.Rows(WS.UsedRange.Find(What:="*", After:=FirstCell, Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row) Set LastCol = WS.Columns(WS.UsedRange.Find(What:="*", After:=FirstCell, Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column) Set LastCell = Application.Intersect(LastRow, LastCol) Set DataRng = WS.Range(FirstCell, LastCell) End Function
Ron De Bruin: http://www.rondebruin.nl/win/s9/win005.htm
Last Function:
Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Long
Select Case choice
Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
On Error Resume Next
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
On Error Resume Next
Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
End Select
End Function
Contextures: http://www.contextures.com/xlfaqApp.html#Unused Reset used range:
Sub DeleteUnused() Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range For Each wks In ActiveWorkbook.Worksheets With wks myLastRow = 0 myLastCol = 0 Set dummyRng = .UsedRange On Error Resume Next myLastRow = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchdirection:=xlPrevious, _ searchorder:=xlByRows).Row myLastCol = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchdirection:=xlPrevious, _ searchorder:=xlByColumns).Column On Error GoTo 0 If myLastRow * myLastCol = 0 Then .Columns.Delete Else .Range(.Cells(myLastRow + 1, 1), _ .Cells(.Rows.Count, 1)).EntireRow.Delete .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete End If End With Next wks End Sub '================================
Sub TestForMergedCells() Dim AnyMerged As Variant AnyMerged = ActiveSheet.UsedRange.MergeCells If AnyMerged = False Then MsgBox "no merged" ElseIf AnyMerged = True Then MsgBox "all merged" ElseIf IsNull(AnyMerged) Then MsgBox "mixture" Else MsgBox "never gets here--only 3 options" End If End Sub '===================================== Chip Person: http://www.cpearson.com/excel/LastCell.aspx Has similar function
When just want to specify the last row # ie for data lookup etc