Method 1 – Use Sheet Name to Select First Visible Cell in Filtered Range with Excel VBA
STEPS:
- 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.
- Select Insert and then Module in the Visual Basic It will open the Module window.
- Enter the 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
We have inserted FirstVisibleCell inside the parentheses of Worksheets() as that’s the name of our sheet. You need to edit this portion and insert the name of the sheet where you want to apply the code. Our dataset starts from Column B, so we have entered B in the third line inside the Range() object. If your dataset starts from Column A, then you need to enter A in place of B.
- After entering the code, press Ctrl + S to save the code.
- Press the F5 key to Run the code.
- You can also run the code from the Macro box.
- Go to the Developer tab and select Macros. It will open the Macro box.
- In the Macro box, select the desired macro and Run it.
- Excel will select cell B7, which is the first visible cell in the filtered range.
Read More: How to Select Visible Cells in Excel with VBA
Method 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. 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:
- Press the Alt + F11 keys to open the Visual Basic window.
- Select Insert and select Module in the Visual Basic window.
- Enter the following 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
- 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.
Method 3 – Insert VBA to Select First Visible Cell in Filtered Range with ActiveCell Object in Excel
STEPS:
- Navigate to the Developer tab and select Visual Basic. Or, you can press Alt + F11.
- Select Insert >> Module in the Visual Basic window to open the Module window.
- Enter the following 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 entered B4 in the second line of the code. If the first header of your dataset starts from Cell A5, you need to enter A5 in place of B4.
- After typing the code, press Ctrl + S to save it.
- Press the F5 key to run the code.
- You can see the selection of the first visible cell in the filtered range.
Method 4 – Choose First Visible Cell in Filtered Range Utilizing Command Button with Excel VBA
We will insert a command button in the Excel sheet to select the first visible cell in a filtered range.
STEPS:
- Go to the Developer tab and select Design Mode.
- Click on the Insert option in the Developer 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 as shown below.
- After drawing the command button, double-click on it to open the Code window.
- Enter 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 entering the code, press Ctrl + S to save it.
- Close the Code and Visual Basic window.
- Go to the Developer tab and deselect Design Mode.
- Click on the Command Button and Excel will select the first visible cell in the filtered range.
Method 5 – Place VBA Inside Immediate Window to Select First Visible Cell in Filtered Range
STEPS:
- Right-click on the sheet name in the Sheet Tab. It will open a menu.
- Select View Code.
- Select View and select Immediate Window in the Visual Basic window.
- Alternatively, you can press Ctrl + G to open it.
- Enter the code in the Immediate Window:
ThisWorkbook.ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select- Place the cursor at the end of the code as shown below.
- After placing the cursor, hit Enter.
- Excel will select the first visible cell in the filtered range.
Read More: Excel VBA: Select Visible Cells After Autofilter
Download Practice Book
