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

Leave a Reply

avatar
VBA Code to Count Cells by Color
VBA TRICKS
VBA Code to Count Cells by Color

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

VBA Code to Sum Cells by Color
VBA TRICKS
VBA Code to Sum Cells by Color

Recently some of our subscribers have requested us to share a VBA code that can sum the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to sum the cells with specific color and returns the total sum of the matching color cells

FREE VBA UTILITIES
35
VBA Tool to Track Start and End Time (Time & Motion Tracker)

Here is one more wonderful free tool from ExcelSirJi.com which makes your life easy. Time & Motion Tracker helps you to track Start and End time of any type of transaction or activity. The good thing is, it is VBA based tool which helps you to protect from manual manipulation in the data by the user. It is also easy to use, just click on Start (shortcut: Ctrl+Shift+A) or Stop (Ctrl+Shft+S) buttons to record the time

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn