WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA UsedRange

VBA UsedRange

By Twinkle SethiTwinkle Sethi | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

UsedRange, as the name suggests, are the ranges which as some kind of values in them, the empty cells are not included in the used ranges, so in VBA Used ranges is the property of the range object in VBA for those range of cells in rows and columns which are not empty and have some values in it.

UsedRange in VBA Excel

The UsedRange in VBA is a property of the worksheet that returns a range object representing the range used (all Excel cells used or filled in a worksheet) on a particular worksheet. It is a property representing the area covered or bounded by top-left used cell and last right used cells in a worksheet.

We can describe ‘Used cell’ as a cell containing any formula, formatting, value, etc. We can also select the last used cell by pressing CTRL+END keys on the keyboard.

Following is an illustration of a UsedRange in a worksheet:

VBA UsedRange (Worksheet)

We can see in the above screenshot that the UsedRange is A1:D5.

Examples of Excel VBA UsedRange Property

Let us look at some examples below to see how the UsedRange Property in a worksheet can be used to find the used range in VBA:

You can download this VBA UsedRange Excel Template here – VBA UsedRange Excel Template

Example #1

Let us say we have an Excel file containing two worksheets, and we wish to find and select the used range on Sheet1.

Let us see what the Sheet1 contains:

VBA UsedRange Example 1

We use the UsedRange property in the VBA Immediate window to accomplish this task. VBA immediate window is a tool that helps to get information about Excel files, quickly execute or debug any VBA code, even if the user is not writing any macros. It is located in the Visual Basic Editor and can be accessed as follows:

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Go to the Developer tab Excel, and then click on Visual Basic Editor, or press Alt+F11 to open the Visual Basic Editor window.

VBA UsedRange Example 1-1

On doing this, a window opens as follows:

VBA UsedRange Example 1-2

  • Press Ctrl+G to open the immediate window, and type the code.

The immediate window looks like:

VBA UsedRange Example 1-3

  • The following code will select the used range on Sheet1.

Code:

?Worksheets("Sheet1").Activate
True
?ActiveSheet.UsedRange.Select
True

The first statement of the code will activate Sheet1 of the file, and the second statement will select the used range in that active sheet.

VBA UsedRange Example 1-4

On writing this code, we see that the range used in Sheet1 gets selected as follows:

VBA UsedRange Example 1-5

Example #2

Now, let’s say in this example, we wish to find the total number of rows used in Sheet1.To do this, we follow the below steps:

  • Create a macro name in the module.

Code:

Sub TotalRows()

End Sub

VBA UsedRange Example 2

  • Define the variable TotalRow as Integer in VBA:

Code:

Sub TotalRows()

 Dim TotalRow As Integer

End Sub

Example 2-1

  • Now assign the variable TotalRow with the formula to calculate a total number of rows:

Code:

Sub TotalRows()

 Dim TotalRow As Integer

 TotalRow = ActiveSheet.UsedRange.Rows.Count

End Sub

Example 2-2

  • Now the resultant value of TotalRow can be displayed and returned using a VBA message box (MsgBox) as follows:

Code:

Sub TotalRows()

 Dim TotalRow As Integer

 TotalRow = ActiveSheet.UsedRange.Rows.Count

 MsgBox TotalRow

End Sub

VBA UsedRange Example 2-3

  • Now we run this code manually or by pressing F5, and we get the total number of rows used in Sheet1 displayed in a Message Box as follows:

VBA UsedRange Example 1-1

So, we can see in the above screenshot that ‘5’ is returned in the message box, and as we can see in Sheet1, the total number of rows in the used range is 5.

Example #3

Similarly, if we wish to find the total number of columns used in Sheet1, we will follow the same steps as above except for a slight change in the code as follows:

Code:

Sub TotalCols()

 Dim TotalCol As Integer

 TotalCol = ActiveSheet.UsedRange.Columns.Count

 MsgBox TotalCol

End Sub

VBA UsedRange Example 3

Now when we run this code manually or by pressing F5, we get the total number of columns used in Sheet1 displayed in a Message Box as follows:

VBA UsedRange Example 2-1

So, ‘4’ is returned in the message box, and as we can see in Sheet1, the total number of columns in the used range is 4.

Example #4

Now, let’s say we wish to find the last used row and column number in Sheet2 of the file. Let us see what the Sheet2 contains:

VBA UsedRange Example 4

To do this, we follow the below steps:

  • Create a macro name in the module.

Code:

Sub LastRow()

End Sub

Example 4-1

  • Define the variable LastRow as Integer.

Code:

Sub LastRow()

 Dim LastRow As Integer

End Sub

Example 4-2

  • Now assign the variable LastRow with the formula to calculate the last used row number:

Code:

Sub LastRow()

  Dim LastRow As Integer

  LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

End Sub

VBA UsedRange Example 4-3

The SpecialCells Method in Excel VBA returns a range object that represents only the types of cells specified. The syntax for the SpecialCells method is:

RangeObject.SpecialCells (Type, Value)

In the above code, xlCellTypeLastCell: represents the last cell in the used range.

Note: ‘xlCellType’ will even include empty cells that have had the default format of any of their cells changed.
  • Now the resultant value of LastRow number can be displayed and returned using a message box (MsgBox) as follows:

Code:

Sub LastRow()

 Dim LastRow As Integer
 
 LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

 MsgBox LastRow

End Sub

Example 4-4

  • Now we run this code manually or by pressing F5, and we get the last used row number in Sheet2 displayed in a Message Box as follows:

VBA UsedRange Example 3

So, we can see in the above screenshot that ‘12’ is returned in the message box, and as we can see in Sheet2, the last used row number is 12.

Similarly, if we wish to find the last used column number in Sheet2, we will follow the same steps as above except for a slight change in the code as follows:

Code:

Sub LastCol()

  Dim LastCol As Integer

  LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
 
  MsgBox LastCol

End Sub

Example 4-5

Now when we run this code manually or by pressing F5, we get the last used column number in Sheet2 displayed in a Message Box as follows:

VBA UsedRange Example 4

So, we can see in the above screenshot that ‘3’ is returned in the message box, and as we can see in Sheet2, the last used column number is 3.

Things to Remember About VBA UsedRange

  • VBA UsedRange is a rectangle range.
  • VBA UsedRange includes cells having any data or being formatted etc.
  • Excel VBA UsedRange does not necessarily include the top-left cell of the worksheet.
  • UsedRange does not necessarily consider the active cell as used.
  • UsedRange can be used to find the last used row in VBA and to reset the used range, etc.
  • Pressing the shortcut Excel keys CTRL+SHIFT+ENTER on a keyboard can be used to extend the selection from an active cell to the last used cell on a worksheet.

Recommended Articles

This has been a guide to VBA UsedRange. Here we will show you how the UsedRange Property in a worksheet to find the used rows and columns in Excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA Filter
  • Excel VBA Activate Sheet
  • VBA Val
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA UsedRange Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More