Excel VBA: Select Visible Cells After Autofilter (4 Examples)

The following dataset has 4 columns displaying people’s Names, where they are From, their Age, and Income.

Method 1 – Selecting Visible Cells after Applying Autofilter in an Existing Worksheet

Steps:

Go to the Developer tab on your ribbon.
Select Visual Basic from the Code group.

The VBA window will open up. Now click on the Insert tab in it.
Select Module from the drop-down list.

Double-click the module to select it.
Enter the following code in the module:

Sub Select_AutoFiltered_VisibleRows_ExistSheet()
Dim FilterValues As String
FilterValues = "Texas"
ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues
ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select
End Sub

Code Breakdown:

Dim FilterValues As String

This portion declares the variable.

FilterValues = "Texas"

We are entering a string value for the FilterValues in this section. This will be the filter value later on. If you need to filter with different values, put the value here.

ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues

We are now selecting the range B4:E14 and using the .autofilter method in it. By selecting the field and Criteria1 parameter, we are choosing to filter the FilterValues value from the second column. You can change the field value to the serial of the column you want to filter from. Also, you can change your range if you have a different range.

ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select

After the filter is complete, this line of code selects them.

Press F5 to run the code.

The spreadsheet will now look like this. The VBA code will automatically auto-filter and select the visible cells in the Excel spreadsheet.

Method 2 – Using Excel VBA InputBox Function to Select Visible Cells After Autofilter

Steps:

Go to the spreadsheet where you want to perform the task.
Go to the Developer tab on your ribbon.
Select Visual Basic from the Code group.

The VBA window will open up. Now select the Insert tab in it.
Select Module from the drop-down list.

Double-click the module to select it.
Enter the following code in the module:

Sub Select_AutoFiltered_VisibleRows_InputBox()
Dim FilterValues As String
FilterValues = InputBox("Enter the Name of the State")
ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues
ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select
End Sub

Code Breakdown

Dim FilterValues As String

This portion declares the variable.

FilterValues = InputBox("Enter the Name of the State")

This line enters an input box for the FilterValues in this section. This will be used as the filter value later on.

ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues

We are now selecting the range B4:E14 and using the .autofilter method. By selecting the field and Criteria1 parameter, we are choosing to filter the FilterValues value from the second column. Changing the field value here might result in some complications. But you can change your range if you have a different range.

ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select

After the filter is complete, this line of code selects them.

Press F5 to run the code.
The input box will pop up over the spreadsheet. Enter the parameter you need in the input box.

Click on OK.

This will select all visible cells after applying auto-filter in the Excel spreadsheet using VBA.

Read More: How to Deselect in Excel VBA

Method 3 – Selecting Visible Cells After Autofilter by Choosing Criteria with VBA InputBox

Steps:

Go to the spreadsheet you want to run the code.
Select the Developer tab on your ribbon.
Select Visual Basic from the Code group.

The VBA window will open up. Now select the Insert tab in it.
Select Module from the drop-down list.

Select the module by double-clicking it from the left side of the window.
Enter the following code in the module:

Sub Select_Visible_Cells_After_Autofilter()
Dim Filter_Parameter As String
Filter_Column_No = InputBox("Enter Column Number to Filter")
Filter_Parameter = InputBox("Enter Value to Filter")
ActiveSheet.Range("B4:E50").AutoFilter
ActiveSheet.Range("B4:E50").AutoFilter field:=Filter_Column_No, Criteria1:=Filter_Parameter
ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select
End Sub

Code Breakdown

Dim Filter_Parameter As String

This portion declares the variables.

Filter_Column_No = InputBox("Enter Column Number to Filter")
Filter_Parameter = InputBox("Enter Value to Filter")

We use two input boxes to take in the column number and the filter values.

ActiveSheet.Range("B4:E50").AutoFilter

ActiveSheet.Range("B4:E50").AutoFilter field:=Filter_Column_No, Criteria1:=Filter_Parameter

In this section, we select the range B4:E14 and use the .autofilter method. By selecting the field and Criteria1 parameter, we choose to filter the Filter_Parameter value from the Filter_Column_No column. Keep in mind that these values are taken from the input box values. But you can change your range if you have a different range.

ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Select

After the filter is complete, this line of code selects them. Also, use the same range as the previous one if you changed it to a bigger one.

Press F5 to run the code.
An input box will appear asking for the column number. Select the number of the column you want to filter from here.

After clicking on OK, another input box will appear, asking for the value. Insert the value you want to filter from the column here.

Click on OK.

This can select visible cells after using the auto filter from different columns with different values every time in Excel using VBA.

Method 4 – Selecting and Copying Visible Cells in a New Sheet After Applying Autofilter

Steps:

Go to the spreadsheet and select the Developer tab in it.
Select Visual Basic from the Code group.

Select Insert in the VBA window.
Select Module from the drop-down list.

Select the cell again by double-clicking it.
Enter the following code in the module:

Sub Copy_AutoFiltered_VisibleRows_NewSheet()
Dim FilterValues As String
FilterValues = "Texas"
ActiveSheet.Range("B4:E14").AutoFilter
ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues
ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Range("B2").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
End Sub

Code Breakdown

Dim FilterValues As String

This portion of the code is used to define variables. We have only one here, the FilterValues.

FilterValues = "Texas"

We defined the value of the variable FilterVales as “Texas”.

ActiveSheet.Range("B4:E14").AutoFilter

ActiveSheet.Range("B4:E14").AutoFilter field:=2, Criteria1:=FilterValues

This portion of the code filters the rows with “Texas” values from the second column of the range B4:E14.

ActiveSheet.Range("B4:E14").SpecialCells(xlCellTypeVisible).Copy

The line copies the visible cells after filtering.

Sheets.Add After:=ActiveSheet

This line adds a new sheet after the selected sheet.

ActiveSheet.Range("B2").PasteSpecial Paste:=xlPasteAllUsingSourceTheme

Finally, this line of code pastes the copied code into the new spreadsheet

Press F5 to run the code.

In the main sheet, the dataset will be autofiltered.

In the next sheet, the visible cells will be copied and pasted.

You can copy selected visible cells after autofilter using Excel VBA.

Download the Practice Workbook

Download the workbook to practice.

Select Visible Cells After Autofilter.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA: Select Visible Cells After Autofilter (4 Examples)

Method 1 – Selecting Visible Cells after Applying Autofilter in an Existing Worksheet

Method 2 – Using Excel VBA InputBox Function to Select Visible Cells After Autofilter

Method 3 – Selecting Visible Cells After Autofilter by Choosing Criteria with VBA InputBox

Method 4 – Selecting and Copying Visible Cells in a New Sheet After Applying Autofilter

Leave a reply Cancel reply