Find Last Cell (Row and Column) With Data in Excel- Full Proff

'Find Last Cell with data

'Use this Global Variables in Module
Dim LastRowWithData As Double
Dim LastColWithData As Double

'-----Call below Function from any part of the code and pass Worksheet object as parameter for which you want to find last cell--------
'----use Global varaibles to get Last Row with data and Last Column with data
'Function
Public Sub LastCellsWithData(Xls As Worksheet)
Xls.Activate
Dim ExcelLastCell As Object
Dim row As IntegerDim col As Integer

'SpecailCells is Inbuoild Function
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it
LastRowWithData = ExcelLastCell.rowrow = ExcelLastCell.row

Do While Application.CountA(ActiveSheet.Rows(row)) = 0 And row <> 1
row = row - 1
Loop

LastRowWithData = row

' Determine the last column with data

LastColWithData = ExcelLastCell.Column
col = ExcelLastCell.Column
Do While Application.CountA(ActiveSheet.Columns(col)) = 0 And col <> 1
col = col - 1
Loop
LastColWithData = col
' Column number
End Sub