'*****************************************************
 'The UsedRegion function below has the single optional argument
 ''StartCell', where StartCell is the top-left of the range you want to
 'be considered. StartCell is used when you wish to be selective and
 'only consider portions of the UsedRegion. In the absence of a given
 'StartCell, the range A1 is taken to be the default StartCell i.e. the
 'entire UsedRegion is then considered as being the default region.
 
 '*****************************************************
 
Option Explicit 
 
 '<< FUNCTION TO DETERMINE THE REGION CONTAINING VALUES >>
 
Function UsedRegion(Optional StartCell As Range) As Range 
     
    Dim LastCol&, LastRow&, Junction As Range 
     
     '//empty sheet = error
    On Error GoTo NotFound 
     
     '//find the last column
    LastCol = Cells.Find("*", SearchOrder:=xlByColumns, _ 
    LookIn:=xlValues, SearchDirection:=xlPrevious).Column 
     
     '//find the last row
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, _ 
    LookIn:=xlValues, SearchDirection:=xlPrevious).Row 
     
     '//set the default start cell as A1
    If StartCell Is Nothing Then Set StartCell = [A1] 
     
    If StartCell.Row > LastRow Or StartCell.Column > LastCol Then 
         '//end if the start cell is outside the used region
        MsgBox "Your start cell is outside the used region" 
        End 
    Else 
         '//continue if the start cell is inside the used region
        Set Junction = Intersect(Columns(LastCol), Rows(LastRow)) 
        Set UsedRegion = Range(StartCell.Address & ":" & Junction.Address) 
    End If 
    Exit Function 
     
NotFound: 
     '//empty sheet
    MsgBox "There are no values on this sheet", , "No Used Region" 
     '//message alert already given (just above)
    Application.DisplayAlerts = False 
    End 
End Function 
 
 '*****************************************************
 
 '<< A SIMPLE EXAMPLE USAGE >>
 
Sub UsedRegionAddy() 
     
     '//give the used region address
    MsgBox "The Used Region address is " & UsedRegion.Address 
     
End Sub 
 
 '*****************************************************
 
 '<< ANOTHER SIMPLE EXAMPLE USAGE >>
 
Sub EntireUsedRegion() 
     
     '//colour the UsedRegion from A1 to the Junction
    UsedRegion.Interior.ColorIndex = 35 
     
End Sub 
 
 '*****************************************************
 
 '<< A LESS SIMPLE EXAMPLE USING THE OPTIONAL ARGUMENT >>
 
Sub TheUsedRegion() 
     
     '//more than one cell selected
    If Selection.Cells.Count > 1 Then 
        MsgBox "Please select one cell only...", , "Ambiguous - Need One Cell" 
        Exit Sub 
    End If 
     
     '//select the area starting from selection to the junction
    UsedRegion(ActiveCell).Select 
     
    If Selection.Cells.Count = 1 Then 
        MsgBox "(Only one cell in region)" 
    End If 
     
End Sub 
 '*****************************************************