VBA Code to Find Last Column or Row of a Sheet

Sometimes as a developer, you need to take actions in Excel sheets based on last row or column. In Excel, there are two kinds of last cells available:

  • The last cell of the sheet which is XFD1048576 for Excel 2007 or above and IV65536 for Excel 2003
  • The last used cell of the sheet which can also be found using Ctrl+End keys

VBA Code to Find Last Column or Row of a Sheet

'This function gets last cell information of the sheet
Sub GetLastCellInfo()
    'Last Row of the Sheet
    Sheet1.Range("E4").Value = Sheet1.Rows.Count
    'Last Column of the Sheet
    Sheet1.Range("E5").Value = Sheet1.Columns.Count
    'Last Used Row of the Sheet
    Sheet1.Range("E6").Value = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
    'Last Used Column of the Sheet
    Sheet1.Range("E7").Value = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Column
End Sub

To use this code in your Excel file, follow below steps:

  1. Open an Excel file
  2. Press Alt+F11
  3. Insert a Module (Insert>Module) from menu bar
  4. Paste the code in the module
  5. Now add a shape in Excel sheet
  6. Give a name to the shape like ‘Get Last Cell Info’
  7. Select ‘GetLastCellInfo’ from the list and click on ‘Ok’ buttonVBA Code to Find Last Column or Row of a Sheet
  8. Select ‘GetLastCellInfo’ from the list and click on ‘Ok’ buttonVBA Code to Find Last Column or Row of a Sheet
  9. Done, click on the shape to get last cell informationVBA Code to Find Last Column or Row of a Sheet
Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *