This tutorial will demonstrate 8 examples of using VBA to select the Used Range in a column in Excel. Generally, the UsedRange property in Excel represents the part of a worksheet that has data on it. To illustrate the examples clearly, we will apply the UsedRange property in a particular dataset for all the examples.
VBA to Select UsedRange in Column: 8 Practical Examples
In the following image, we can see the dataset that we will use for all the examples. The dataset contains the names of Salespeople, their Location, Region, and Total Amount of sales. In this dataset, the used range will be considered including the heading. So, the used range in the following dataset is (B2:E15).
1. Select UsedRange in Column with VBA in Excel
First and foremost, we will select all the columns from our dataset. To do this we will use the VBA select UsedRange property in columns. Let’s see the steps to perform this method.
STEPS:
- To begin with, right-click on the active sheet named ‘Select_Columns’.
- In addition, select the option ‘View Code’.
- Then, the above action opens a blank VBA code window for that worksheet. We can also get this code window by pressing Alt + F11.
- Next, type the following code in that code window:
Sub Select_Columns()
Sheets("Select_Columns").Select
ActiveSheet.UsedRange.Select
End Sub- After that, click on the Run or press the F5 key to run the code.
- Lastly, we get the result like the following image. We can see that the used range in columns from our dataset is selected now.
Read More:Â How to Use VBA to Select Range from Active Cell in Excel
2. Use VBA to Copy Entire UsedRange in Column
In the second example, we will use VBA to copy the entire UsedRange in the columns from our dataset. Generally, we use this method to copy a specific region from our dataset. We need to follow the below steps to perform this method.
STEPS:
- First, go to the active worksheet tab named ‘Copy’.
- Next, right-click on that tab and select the option ‘View Code’.
- It will open a blank VBA code window for the current worksheet. Another way to get this window is to press Alt + F11 from the keyboard.
- Then, Insert the below code in that code window:
Sub Copy_UsedRange()
ActiveSheet.UsedRange.Copy
End Sub- Now, to run the code click on the Run or press the F5Â key.
- Finally, we can see the result like the following. Also, we can see a borderline around the used range. It indicates that the code has copied data inside this border.
3. Count Number of Columns in UsedRange Using VBA
In the third example, we will count the number of columns in our dataset using the Excel VBA select Used Range method in the column. This example will return the total number of columns inside the used range in our dataset in a message box. Follow the below steps to execute this method.
STEPS:
- Firstly, select the active sheet named ‘Count_Columns’.
- Secondly, right-click on the active sheet name and click on the option ‘View Code’.
- The above command opens a blank VBA code window for the active worksheet. We can also get the code window by pressing Alt + F11 from the keyboard.
- Thirdly, input the following code in that blank code window:
Sub Count_Columns()
MsgBox ActiveSheet.UsedRange.Columns.Count
End Sub- Next, click on the Run or press the F5 key to run the code.
- Lastly, we get the result in a message box. The number of columns in the used range is 4.
4. Excel VBA to Count Number of Last Column in Used Range
In the previous method, we extracted the number of the last column in the used range. However, in this example, we will determine the number of the last column in the used range across the entire worksheet using the VBA select UsedRange property. Let’s see the steps that we need to follow to perform this action.
STEPS:
- To begin with, right-click on the active sheet named ‘Last Column’.
- Next, select the option ‘View Code’.
- So, the above command opens a blank VBA code window for that worksheet. An alternative way to open that code window is to press Alt + F11.
- After that, insert the following code in that code window:
Sub Column_Last()
Dim Column_Last As Integer
Column_Last = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
MsgBox Column_Last
End Sub- Now, click on the Run or press the F5 key to run the code.
- In the end, we get our result in a message box. The last column in the used range is the 5th column of the worksheet.
5. Select Last Cell of Last Column from UsedRange with VBA
In the fifth example, we will use the VBA select Used Range property to select the last cell of the last column in an Excel sheet. To illustrate this example, we will continue with our previous dataset. Now, take a look at the steps to do this method.
STEPS:
- First, select the active sheet named ‘Last_Cell’.
- Next, right-click on that sheet name. Select the option ‘View Code’.
- Then, we get a blank VBA code window. Also, we can press Alt + F11 to open that code window.
- After that, type the following code in that code window:
Sub Last_Cell_UsedRange()
Dim wcol As Long
Dim wrow As Long
wrow = ActiveSheet.UsedRange.Rows.Count
wcol = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.UsedRange.Select
Selection.Cells(wrow, wcol).Select
End Sub- Now, to run the code click on the Run or press the F5.
- Finally, we can see the result in the following image. The selected last cell of the last column is cell E15.
Read More: Excel VBA: Select Range with Offset Based on Active Cell
6. Find Cell Range of Selected UsedRange with Excel VBA
In this example, we will apply VBA to find the cell range of the selected used range in an Excel worksheet. We will use the VBA code for all the columns in our used range. The code will return the cell range as well as the address of the column in the used range. Follow the below steps to perform this action.
STEPS:
- In the beginning, right-click on the active sheet tab named ‘Find Cell Range’.
- Secondly, select the option ‘View Code’.
- It will open a blank VBA code window. Another method to open this code window is to press Alt + F11.
- Thirdly, enter the following code in that code window:
Sub Find_UsedRange()
MsgBox ActiveSheet.UsedRange.Address
End Sub- Then, to run the code click on the Run or press the F5Â key.
- In the end, a message box like the following image shows the result.
7. Insert VBA UsedRange Property to Count Empty Cells
In this example, we will use the VBA select UsedRange property to count empty cells in an Excel sheet. Sometimes we might have empty cells in the used range of our dataset. We can easily count the number of those empty cells by using the UsedRange property. Let’s see the steps to perform this example.
STEPS:
- First, right-click on the active sheet tab named ‘Empty_Cells’.
- Next, select the option ‘View Code’.
- The above action opens a blank VBA code window. An alternative way to open that code window is to press Alt + F11.
- Then, insert the following code in that code window:
Sub Count_Empty_Cells()
Dim wCell As Range
Dim wRange As Range
Dim d As Long
Dim j As Long
Set wRange = ActiveSheet.UsedRange
For Each wCell In ActiveSheet.UsedRange
d = d + 1
If IsEmpty(wCell) = True Then
j = j + 1
End If
Next wCell
MsgBox "Total number of used cell(s)in 4 used columns is " & d & _
" and out of those " & _
j & " cell(s) are empty."
End Sub- After that, click on Run or press the F5 key to run the code.
- Finally, we will get the result in the message box. The message box will display the number of total cells and blank cells in our used range.
8. VBA UsedRange to Locate First Empty Cell in Column in Excel
In the last example, we will use the Excel VBA to select the Used Range property in the column to locate the first empty cell in our Excel worksheet. This method will locate the first empty cell of a particular column. The empty cell will always be outside of the used range of the dataset. So, if any cell is blank or empty in the used range it will not be considered in this method. Now, follow the below steps to perform this method.
STEPS:
- To begin with, right-click on the active sheet tab named ‘First_Empty’.
- In addition, select the option ‘View Code’.
- It will open a blank VBA code window. We can also press Alt + F11 to open that code window.
- Furthermore, type the following code in the blank VBA code window:
Public Sub First_Empty_Cell()
ActiveSheet.Range("E" & ActiveSheet.Rows.Count) _
.End(xlUp).Offset(1, 0).Value = "FirstEmptyCell"
End Sub- Then, to run the code click on the Run or press the F5Â key.
- Lastly, the above code will insert the value ‘FirstEmptyCell’ in cell E16. It is the first empty cell of column E after the used range of the dataset.
Download Practice Workbook
We can download the practice workbook from here.
Conclusion
In a nutshell, this tutorial shows 8 examples to use the VBA select UsedRange property in an Excel sheet. To set your skills to the test, download the practice worksheet used for this article. Please feel free to comment in the box below if you have any queries. Our team will try to react to your message as soon as possible.
