Tuesday, February 9, 2016

Excel VBA to get the Last Used Row and Last Used Column.


Here's how to determine the last used row and last used column in excel.
        • Get the last row in one column: End(xlUp)
        • Get the last row in one column: UseRange.Rows.Count
        • Get the last column in one row: End(xlToLeft)
        • Get the last column in one row: UseRange.Columns.Count
Get the last row in one column: Use End(xlUp)
Sub getLastRow()
      Dim lRow As Long
      lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
      MsgBox lRow
End Sub



Get the last row in one column: UseRange.Rows.Count
Sub getLastRow2()
      Dim lRow As Long
      lRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
      MsgBox lRow
End Sub



Get the last column in one row: Use End(xlToLeft)

Sub getLastColumn()
     Dim lColumn As Long
     lColumn = ActiveSheet.Cells(Rows.Count, "A").End(xlToLeft).Row
     MsgBox lColumn
End Sub


Get the last column in one row: UseRange.Columns.Count
Sub getLastColumn2()
     Dim lColumn As Long
     lColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
     MsgBox lColumn
End Sub 

Link: Excel Shortcut Keys

No comments:

Post a Comment