In this article, we will learn to select the first visible cell in a filtered range with Excel VBA. We can select the first visible cell in a filtered range with a few lines of code in VBA. Today, we will explain 5 ideal examples of Excel VBA to select the first visible cell. These examples contain different VBA codes and methods. After reading the whole article, you can select the visible cell in a filtered range easily. So, without further delay, let’s start the discussion.
Select First Visible Cell in Filtered Range with Excel VBA: 5 Ideal Examples
To explain the examples, we will use a dataset that contains information about the Name, Department, and Performance of some employees of a company.
After applying a filter based on the department, the dataset will look like the picture below. In this filtered range, we will apply the VBA code and select the first visible cell.
1. Use Sheet Name to Select First Visible Cell in Filtered Range with Excel VBA
In the first example, we will use a code where you need to insert the sheet name to get the desired result. Let’s follow the steps below to know how we can implement the example.
STEPS:
- First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- Alternatively, you can also press the Alt + F11 keys to open the Visual Basic window.
- Secondly, select Insert and then Module in the Visual Basic window. It will open the Module window.
- Now, type the below code in the Module window:
Sub Select_First_Visible_Cell()
With Worksheets("FirstVisibleCell").AutoFilter.Range
   Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
End Sub
In this case, our sheet name was FirstVisibleCell. So, we have inserted this name inside the parentheses of Worksheets(). You need to edit this portion and insert the name of the sheet where you want to apply the code. Also, our dataset started from Column B. So, we have typed B in the third line inside the Range() object. If your dataset starts from Column A, then you need to type A in place of B.
- After typing the code, press Ctrl + S to save the code.
- Then, press the F5 key to Run the code.
- Otherwise, you can also run the code from the Macro box.
- To do so, go to the Developer tab and select Macros. It will open the Macro box.
- In the Macro box, select the desired and Run it.
- As a result, you will see Excel has selected cell B7 which is the first visible cell in the filtered range.
Read More: How to Select Visible Cells in Excel with VBA
2. Apply Excel VBA with ActiveSheet Object to Get First Visible Cell in Filtered Range
A VBA code can be written with different objects for the same purpose. In the previous example, we inserted the sheet name where we want to apply the code. But in this case, we will apply the code in the active sheet. So, we will use the ActiveSheet object. Also, you don’t need to specify the column number. You can use the code without changing anything in the active sheet. The steps of this example are the same as in Example 1. You just need to change the code.
STEPS:
- Firstly, press the Alt + F11 keys to open the Visual Basic window.
- In the second step, select Insert and then, select Module in the Visual Basic window.
- After that, type the below code in the Module window:
Sub Select_First_Visible_Cell2()
Dim iRng As Range
With ActiveSheet.AutoFilter
   Set iRng = .Range.Resize(.Range.Rows.Count - 1).Offset(1, 0)
   iRng.SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With
End Sub
- Now, press Ctrl + S to save the code.
- After saving the code, press the F5 key to run it.
- As a result, Excel will select the first visible cell in the filtered range.
3. Insert VBA to Select First Visible Cell in Filtered Range with ActiveCell Object in Excel
In the third example, we will use the ActiveCell object to select the first visible cell in a filtered range. The process of this example is similar to the previous one. We just need to type a different code in the Module window. So, let’s observe the steps quickly to implement the example.
STEPS:
- In the first place, navigate to the Developer tab and select Visual Basic. Or, you can press the Alt + F11 keys for this purpose.
- After that, select Insert >> Module in the Visual Basic window to open the Module window.
- Now, type the code in the Module window:
Sub Select_First_Visible_Cell3()
Range("B4").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
End Sub
In this code, you need to change the second line according to your dataset. In our dataset, the header of the filtered range started from Cell B4. So, we have typed B4 in the second line of the code. If the first header of your dataset starts from Cell A5, then you need to type A5 in place of B4.
- After typing the code, press Ctrl + S to save it.
- Now, press the F5 key to run the code.
- Finally, you can see the selection of the first visible cell in the filtered range.
4. Choose First Visible Cell in Filtered Range Utilizing Command Button with Excel VBA
In Excel, we can insert a code inside the Command Button and utilize it for the desired purpose. In this example, we will insert a command button in the Excel sheet to select the first visible cell in a filtered range. Let’s pay attention to the steps below to learn more about this example.
STEPS:
- In the first place, go to the Developer tab and select Design Mode.
- Secondly, click on the Insert option in the Developer tab. A drop-down menu will appear.
- Select the Command Button icon from the ActiveX Controls section.
- As a result, the mouse pointer will turn into a black plus (+) symbol.
- Use this black plus (+) symbol to draw the command button in the sheet like the picture below.
- After drawing the command button, double-click on it to open the Code window.
- Now, type the code in the Code window:
Private Sub CommandButton1_Click()
Dim iRng As Range
With ActiveSheet.AutoFilter
   Set iRng = .Range.Resize(.Range.Rows.Count - 1).Offset(1, 0)
   iRng.SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
End With
End Sub
We have used the same code in Example 2. But we are using the Private Sub procedure this time.
- After typing the code, press Ctrl + S to save it.
- Then, close the Code and Visual Basic window.
- In the following step, go to the Developer tab and deselect Design Mode.
- Finally, click on the Command Button and Excel will select the first visible cell in the filtered range.
5. Place VBA Inside Immediate Window to Select First Visible Cell in Filtered Range
In the last example, we will use a macro inside the immediate window to select the first visible cell in a filtered range. This example will get you the results very quickly. Because the code is not long and the steps are very easy to implement. So, let’s follow the steps below to see how we can use the immediate window.
STEPS:
- Firstly, right-click on the sheet name in the Sheet Tab. It will open a menu.
- Select View Code from there.
- Secondly, select View and then, select Immediate Window in the Visual Basic window.
- Alternatively, you can press Ctrl + G to open it.
- At this moment, type the code in the Immediate Window:
ThisWorkbook.ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select- Then, place the cursor at the end of the code like the picture below.
- After placing the cursor, hit the Enter key.
- Instantly, you will see Excel has selected the first visible cell in the filtered range.
Read More: Excel VBA: Select Visible Cells After Autofilter
Download Practice Book
You can download the practice book from here.
Conclusion
In this article, we have 5 ideal examples of Excel VBA to select the first visible cell in a filtered range. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
