Wednesday, 20 March 2013

How To Find Last Used Row and Column in a Worksheet

Excel VBA Macro To Find Last Used Row and Column in a Worksheet
Sub LastRow_UsedInSheet()

MyLastRow = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

MyLastColumn = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious).Column

MsgBox MyLastRow & " Rows Have Used In ActiveSheet " & vbCrLf & _

MyLastColumn & " Columns Have Used In ActiveSheet "

End Sub


Example :

Note:
This method will find only the Used Rows and Columns with data.
We can use this Count while Consolidating the data from the Several Sheets into One Sheet.

Thanks,
TAMATAM

1 comment:

  1. is this alternate for activesheet.cell.specialcells(xlcelltypelastcell).row
    activesheet.cell.specialcells(xlcelltypelastcell).column

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts