VBA Code to Find Last used Column or Row in Excel

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:

VBA Code to Find Last used Column or Row in Excel
'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

VBA Code to Find Last used Column or Row in Excel Steps to follow:-

  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

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

Similar Posts

Leave a Reply

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