Function RealLastCell(TheSheet As Worksheet) As Range
 
   Dim ExcelLastCell As Range
   Dim Row, Col, LastRowWithData, LastColWithData As Integer
 
   Set ExcelLastCell = TheSheet.Cells.SpecialCells(xlLastCell)
 
    LastRowWithData = ExcelLastCell.Row
    Row = ExcelLastCell.Row
 
    Do While Application.CountA(TheSheet.Rows(Row)) = 0 And Row <> 1
       Row = Row - 1
    Loop
 
        LastRowWithData = Row
        LastColWithData = ExcelLastCell.Column
        Col = ExcelLastCell.Column
 
            Do While Application.CountA(TheSheet.Columns(Col)) = 0 And Col <> 1
               Col = Col - 1
            Loop
 
        LastColWithData = Col
 
        Set RealLastCell = TheSheet.Cells(Row, Col)
 
End Function