VBA – Excel – Determine and set data range

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