VBA UsedRange

What is UsedRange in VBA Excel?

VBA UsedRange is a property that refers to the range of cells used in an Excel worksheet. The VBA UsedRange begins from the first to the last cell, where we have some values. The empty cells between the start and end cells are also considered as a used range. For example, look at the following data in Excel.

We have a value in cell A1 and cell E8; in between, there are empty cells. When it comes to VBA UsedRange, the used range will be from A1 to E8.

How to Use UsedRange in VBA Excel?

Now, let us show you how to use VBA UsedRange with a step-by-step approach. We will use the following data.

We have some blank rows, and let us see what happens if we try to select the entire range using the VBA UsedRange property.

Step 1: Create a macro sub-procedure.

Step 2: VBA UsedRange can be used as a property of ActiveSheet. Hence, we will refer to the current worksheet as an active sheet.

Step 3: Entera dot to select the properties related to the ActiveSheet object.

The issue with directly using the ActiveSheet and trying to access the properties and methods related to the active sheet object is we really don’t get to see them in the IntelliSense list.

Hence, users should be aware of all the properties and methods related to the Active Sheet object. To tackle the issue of the IntelliSense list not showing, we can rely on variables.

Step 4: Declare a variable and assign the VBA Worksheet as data type.

Step 5: Since the VBA Worksheet is an Object data type, we must use the “SET” keyword to assign the worksheet reference to the variable.

Now, the variable “Ws” holds the reference of the active sheet.

Step 6: Use the variable name “Ws” and enter a dot.

As we can see, the moment we enter a dot, we can access all the properties and methods related to the active sheet object.

Step 7: Choose the VBA UsedRange property from the IntelliSense list.

Step 8: Once we select the VBA UsedRange property, we need to decide what to do. Suppose we must select the range. Enter another dot and choose the Select method of UsedRange.

Here is the complete code for your reference.

Sub VBA_UsedRange()

    Dim Ws As Worksheet

    Set Ws = ActiveSheet

    Ws.UsedRange.Select

End Sub

Let’s execute this code. It should select the used range in the active sheet.

As we can see, it selects the used range from cell A1 to D8 even though there are many blank rows and cells.

Hence, the functionality of the VBA UsedRange is it takes the first cell, which has a value in the worksheet as a starting point and takes the last cell with a value and treats the entire range as a used range.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples of Excel VBA UsedRange Property

We will show you some practical illustrations to understand the functionality of the VBA UsedRange in different scenarios.

Example #1 – Select the Complete Used Range

Let’s look at the following data in the Excel worksheet.

We have data from the previous example, but we have added one extra column, “Status,” as column “F.” We have purposely left column “E” blank.

Step 1: Start the sub-procedure by naming the macro in the Visual Basic editor window.

Step 2: As we already know, the UsedRange property is available with the ActiveSheet object. If we directly use the ActiveSheet object, we do not get to see the IntelliSense list, which shows all the properties and methods associated with ActiveSheet.

Hence, we define a variable with the Worksheet object as the data type.

Step 3: Once the worksheet variable is defined, we must set it as the Worksheet reference. The worksheet that we are working on is called “Example 1.” So, let us reference this name to the variable we have defined.

Step 4: Now, the variable “MySheet” holds the reference for the worksheet “Example 1.” Let’s use this variable name instead of the complete worksheet reference Worksheets (“Example 1”).

Enter the variable name followed by a dot, and we should see all the properties and methods associated with the worksheet object.

Step 5: From the IntelliSense list, choose the UsedRange property.

Step 6: Once the VBA UsedRange property is chosen, enter a dot to select the next course of action.

Step 7: Assume we need to change the font. Choose the “Font” property of the used range.

Step 8: Once the font property is chosen, we must decide what we must do with the font property of  UsedRange. Assume we need to change the font size, then choose the size property of the font property.

Step 9: Once the size property is chosen, we must set the Size property to a certain number. To set it, enter an equal sign and the desired size number like the following.

Following is the complete code for your reference.

Sub VBA_UsedRange_Ex1()

    ‘Define a variable to set the worksheet reference.

    Dim MySheet As Worksheet

    ‘Set the worksheet reference to the variable.

    Set MySheet = Worksheets(“Example 1”)

    ‘Use the variable and use the used range property to set the font size of the used range.

    MySheet.UsedRange.Font.Size = 8

End Sub

Execute the code. It should change the font size of the used range to 8.

Now, the font size is set to 8. The exciting thing is that though we had a completely blank column in column “E,” the VBA UsedRange property still considers the next column as the used range. Hence, it sets the font size of column “F” as well to 8.

Example #2 – Copy the Entire Used Range to New Worksheet

VBA UsedRange.Copy is the method we can use to copy the entire used range cells. We can insert the copied values into a new sheet as well. Let’s use the following data for this example.

We have this data in a worksheet called “Example 2.” We can use the following code to copy this data from the Example 2 worksheet to a new worksheet.

Sub VBA_UsedRange_Ex2()

    ‘Define a variable to set the worksheet reference

    Dim DataSheet As Worksheet

    ‘Set the worksheet reference to the variable

    Set DataSheet = Worksheets(“Example 2”)

    ‘Use the variable and copy the used range

    DataSheet.UsedRange.Copy

    ‘Insert a new worksheet

    Worksheets.Add After:=ActiveSheet

    ‘Paste in the newly inserted sheet

    ActiveCell.PasteSpecial xlPasteAll

End Sub

Execute the code, and we will get the used range data in a brand-new worksheet.

Example #3 – Count Total Number of Rows in a Used Range

Getting the number of rows used in an Excel worksheet is interesting, especially when the data is scattered across the worksheet.

For example, look at the following data in the Excel worksheet.

We have data in the range A1 to F1, however there are some blank rows and columns. With this scattered data, VBA used range row count is beneficial.

Take a look at the following VBA code.

Sub VBA_UsedRange_Ex3()

    ‘Define a variable to set the worksheet reference

    Dim MySheet As Worksheet

    ‘Set the worksheet reference to the variable

    Set MySheet = Worksheets(“Example 3”)

    ‘Define a variable to get the row count

    Dim Total_Rows As Long

    ‘Get the total row count

    Total_Rows = MySheet.UsedRange.Rows.Count

    ‘Display the count in a message box

    MsgBox Total_Rows

End Sub

Let us explain the code in detail.

• Part #1: We have defined a variable MySheet with Worksheet object reference. Then, we have assigned the worksheet “Example 3” to this variable.
• Part #2: We have defined another variable to assign the total row count. Since we are going to the numerical value, we have assigned the Long data type to it.

Then, we used the worksheet variable name with the UsedRange property.

• Part #3: Finally, we are showing the total row count variable value in a message box. Let’s execute the code. We get the following result.

Since the data is spread across the 11 rows, the VBA UsedRange property has returned the row count as 11.

Example #4 – Count Total Number of Columns in a Used Range

Similarly, we can find the total number of columns as well. Let’s use the same data from Example #3.

The code to find the total number of columns is almost identical to the previous one. However, instead of the “ROWS” property we need to use the “COLUMNS” property.

Look at the following code.

Sub VBA_UsedRange_Ex4()

    ‘Define a variable to set the worksheet reference

    Dim MySheet As Worksheet

    ‘Set the worksheet reference to the variable

    Set MySheet = Worksheets(“Example 3”)

    ‘Define a variable to get the column count

    Dim Total_Columns As Long

    ‘Get the total column count

    Total_Columns = MySheet.UsedRange.Columns.Count

    ‘Display the count in a message box

    MsgBox Total_Columns

End Sub

Only the colored portions are changed from the previous code. We have changed the variable name, and inside the VBA UsedRange property, we have used the Columns property instead of the Rows property.

Let’s execute the code. We get the total number of columns in the VBA UsedRange property.

The total number of columns used in the used range is 6, which includes the blank column as well.

Important Things to Note

• VBA UsedRange is a property accessible only with the ActiveSheet or worksheet object.
• VBA UsedRange throws an Object Not Found error if the referenced worksheet is not valid.
• VBA UsedRange ignores all the blank rows, columns, and cells between the start and end of the data.
• Suppose we directly reference the VBA UsedRange with the worksheet object. In that case, we do not get to see the IntelliSense list, which shows all the properties and methods associated with the VAB UsedRange object.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA UsedRange. with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA UsedRange. Here we learn How to Use UsedRange in VBA Excel along with examples & download excel template. You can learn more from the following articles –

• VBA Range
• VBA Set Range
• VBA Selection Range