VBA Code to Find Last Column or Row of a Sheet

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:

'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:-

Open an Excel file
Press Alt+F11
Insert a Module (Insert>Module) from menu bar
Paste the code in the module
Now add a shape in Excel sheet
Give a name to the shape like ‘Get Last Cell Info’
Select ‘GetLastCellInfo’ from the list and click on ‘Ok’ button
Select ‘GetLastCellInfo’ from the list and click on ‘Ok’ button
Done, click on the shape to get last cell information

Download Practice File

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

Recommended Articles

Productivity and Automation | Excel VBA and Macros

VBA Code to Change Cell Color

Byexcelsirji

VBA Code To Change Cell Color Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this, you can use…

Excel VBA and Macros

VBA Code to Add Border to Excel Range

Byexcelsirji

Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.

Excel VBA and Macros

VBA Code to Delete File

Byexcelsirji

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

Excel VBA and Macros

VBA to Browse Outlook Folder

ByKailash Sharma

VBA to Browse Outlook Folder Outlook is most commonly used emailing application used in the world. Many people spend their entire day on Outlook applications to read and respond to emails. To automate certain rule-based…