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

You can easily apply the autofilter in Excel except using a VBA code. You can also select them manually which you can also use to perform different tasks like copying or conditional formatting etc. We can also do that using VBA, which can make the work easier for datasets with a larger number of columns. In this tutorial, we are going to demonstrate different VBA codes to select visible cells after autofilter in Excel that can be used according to your needs and explain these codes so that you can do your modifications if you need.

The Visual Basic for Applications (VBA) is an event-driven programming language from Microsoft. It can be used to perform various tasks, sometimes otherwise impossible, in Microsoft Office applications.

We are going to go over a total of four different VBA codes in this tutorial. For each method, we are going to use the same dataset as the following figure.

Keep in mind that for any VBA method, you need to have the Developer tab showing on your ribbon. Click here to display the Developer tab on your ribbon. Once you have that, you can follow any of the methods depending on your needs.

1. Select Visible Cells after Applying Autofilter in the Existing Worksheet

In the first method, we are going to use a VBA code that is constructed in such a way that the filter requirements are already embedded in the code. This is especially helpful when you need to filter a specific column by a specific parameter every time. As you don’t need to select parameters every time, the process will be faster if you want to do it over and over again.

Follow these steps to see the usage of the code and the breakdown.

Steps:

• First of all, go to the Developer tab on your ribbon.
• Second, select Visual Basic from the Code group.

• As a result, the VBA window will open up. Now click on the Insert tab in it.
• Then select Module from the drop-down list.

• Now double-click the module to select it.
• Then 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

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

• After that, press F5 on your keyboard to run the code directly.

The spreadsheet will now look like this. You can see the VBA code will automatically autofilter and select the visible cells in the Excel spreadsheet.

Read More: How to Select Visible Cells in Excel with VBA

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

In the second method, we are going to use a code that can give us a similar result. But this time we are going to use an input box to put in the filter values. This is helpful if you want to repeat the process a lot and want to use different filter values at different times from the same column.

Follow these steps to see how we can use the code and the breakdown of it.

Steps:

• First, go to the spreadsheet where you want to perform the task.
• Second, go to the Developer tab on your ribbon.
• Third, select Visual Basic from the Code group.

• Thus, the VBA window will open up. Now select the Insert tab in it.
• After that, select Module from the drop-down list.

• Now double-click the module to select it if it is not selected already.
• Next, 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

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

• After that, press F5 on your keyboard to run the code directly.
• Consequently, the input box will pop up over the spreadsheet. Now put in the parameter you need in the input box.

• Finally, click on OK.

This will select all visible cells after applying autofilter in the Excel spreadsheet using VBA.

Read More: How to Deselect in Excel VBA

3. Select Visible Cells After Autofilter by Choosing Criteria with VBA InputBox

The problem with using the previous code is it can only filter a value from a specific column. If you want to select different values from different columns, you have to change the field value in the code every time manually. In case the filtering and selecting process is repetitive, the process is very tiring. We can work this out by using another input box to filter from in the VBA code.

The disadvantage of using this type of code is that you have to enter the field and filter values every time you run the code. If you have the same value every time you repeat the process, you would be better off using one of the above ones depending on whether you need to change the filter values or not.

Follow these steps to see how we can construct and use such codes.

Steps:

• First of all, go to the spreadsheet you want to run the code.
• Then select the Developer tab on your ribbon.
• After that, select Visual Basic from the Code group.

• As a result, the VBA window will open up. Now select the Insert tab in it.
• Next, select Module from the drop-down list.

• Now select the module by double-clicking it from the left side of the window.
• Then insert 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

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

• Next, press F5 on your keyboard to run the code instantly.
• Thus, 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.

• Then click on OK.

As a result, this can select visible cells after using the auto filter from different columns with different values every time in Excel using VBA.

Read More: Excel VBA to Select First Visible Cell in Filtered Range

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

Now that we are done with different forms of selecting visible cells after auto-filtering in Excel using VBA, we are going to demonstrate a simple use of the code. In this method, we are going to copy the selected portion after filtering- all using VBA codes. We will copy the code to a new spreadsheet using our code.

Follow these steps to see the usage of the code and the breakdown of it.

Steps:

• First, go to the spreadsheet and select the Developer tab in it.
• Then select Visual Basic from the Code group.

• Next, select Insert in the VBA window.
• Then select Module from the drop-down list.

• After that, select the cell again by double-clicking it.
• Then insert 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

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

• Finally, press F5 on your keyboard to run the code instantly.

In the main sheet, the dataset will be autofiltered.

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

This way you can copy selected visible cells after autofilter using Excel VBA.

Read More: Excel VBA: Select All Cells with Data

Download Practice Workbook

You can download the workbook used for the demonstration with the VBA codes from the download link below.

Conclusion

So these were all the codes you can use to select visible cells after auto-filtering in Excel. Hopefully, you can do the same before performing more tasks and can modify these codes comfortably. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.