Public Function RealUsedRange(UsedRange As Range) As Range

    Dim rng As Range
    Dim FirstCell As Range
    Dim iFirstColumn As Long
    Dim iFirstRow As Long
    Dim iColumns As Long
    Dim iRows As Long
    
    On Error GoTo HandleError
    
    Set RealUsedRange = UsedRange
    Set FirstCell = UsedRange.Cells( 1 ,  1 )
    
    iFirstColumn = FirstCell.Column
    iFirstRow = FirstCell.Row

    Set rng = UsedRange.Find("*", FirstCell, xlValues, xlPart, xlByRows, xlPrevious, False)
    iRows = rng.Row
    iRows = iRows - iFirstRow +  1 
    Set rng = UsedRange.Find("*", FirstCell, xlValues, xlPart, xlByColumns, xlPrevious, False)
    iColumns = rng.Column
    iColumns = iColumns - iFirstColumn +  1 
    Set RealUsedRange = FirstCell.Resize(iRows, iColumns)
'    Debug.Print RealUsedRange.Address

HandleExit:
    Exit Function
HandleError:
    Resume HandleExit
    Resume Next
End Function

    Dim i As Long
    i = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,  1 ).End(xlUp).Row

Public Function RealUsedRange(myUsedRange As Range) As Range
    Dim i As Long, r As Long
    Dim c As Byte
    If myUsedRange.Rows.Count =  1  Then Exit Function
    For x =  1  To myUsedRange.Columns.Count
        If myUsedRange.Cells( 2 , x) <> "" And _
            i < myUsedRange.Cells( 1 , x).End(xlDown).Row Then _
            i = myUsedRange.Cells( 1 , x).End(xlDown).Row: c = x
    Next x
    Set RealUsedRange = myUsedRange.Cells( 1 ,  1 ).Resize(i, c)
End Function