VBA UsedRange

UsedRange, as the name suggests, are the ranges that do not include the empty cells in the used ranges as some values. So in VBA, used ranges are the property of the range object for those ranges of cells in rows and columns that are not empty and have some values.

Table of contents

• UsedRange in VBA Excel
  • Examples of Excel VBA UsedRange Property
    • Example #1
    • Example #2
    • Example #3
    • Example #4
  • Things to Remember About VBA UsedRange
  • Recommended Articles

UsedRange in VBA Excel

The UsedRange in VBA is a worksheet property 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 the top-left used cell and the last right-used cells in a worksheet.

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

Following is an illustration of a UsedRange in a worksheet:

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

Learning Microsoft Office VBA allows users to automate tasks and create custom functions in applications like Excel and Word. Those looking to further enhance their data management skills and advance their careers can explore this Microsoft Office VBA Advanced Course.

Examples of Excel VBA UsedRange Property

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

Example #1

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:

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

• Go to the Developer tab Excel, click on Visual Basic Editor, or press Alt+F11 to open the Visual Basic Editor window.

In doing this, a window opens as follows:

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

The immediate window looks like this:

• 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.

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

Example #2

In this example, we wish to find the total number of rows used in Sheet1. To do this, we follow the below steps:

• First, create a Macro name in the module.

Code:

Sub TotalRows() End Sub

• Define the variable TotalRow as an integer variable.":

Code:

Sub TotalRows() Dim TotalRow As Integer End Sub

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

Code:

Sub TotalRows() Dim TotalRow As Integer TotalRow = ActiveSheet.UsedRange.Rows.Count End Sub

• 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

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

So, we can see in the above screenshot that ‘5’ is returned in the message box. As shown 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

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:

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

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

To do this, we follow the below steps:

• First, create a Macro name in the module.

Code:

Sub LastRow() End Sub

• Define the variable LastRow as Integer.

Code:

Sub LastRow() Dim LastRow As Integer End Sub

• 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

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)

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

Note: ‘xlCellType’ will even include empty cells with the default format of any of their cells changed.

• Now, the resultant value of the 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

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

So, we can see in the above screenshot that ‘12’ is returned in the message box. As shown 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

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:

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

This topic opens up many avenues for further exploration. If you’re intrigued by these concepts, this basic VBA course might provide additional insights and hands-on experience.

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 keysCTRL+SHIFT+ 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 article has been a guide to VBA UsedRange. Here, we will show you how the UsedRange Property in a worksheet finds the used rows and columns in Excel VBA, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: -

• VBA Filter
• Excel VBA Activate Sheet
• VBA Val