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

You can easily apply the auto filter in Excel except using a VBA code. You can also select them too 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.


Download Practice Workbook

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


4 Suitable Examples to Select Visible Cells After Autofilter with Excel VBA

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.

dataset to select visible cells after autofilter using excel vba

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


1. Autofilter and Select Visible Cells in Existing Sheet

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.

opening vba window to enter code to select visible cells after autofilter in excel

  • 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.

inserting a module to run vba code that select visible cells after autofilter in excel

  • 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

🔎 Breakdown of the Code

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 in 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 simply selects them.

  • 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.

excel vba select visible cells after autofilter

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


2. Select Visible Cells After Autofilter Using Input Box

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.

inserting a module

  • 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

🔎 Breakdown of the Code

Dim FilterValues As String

This portion declares the variable.

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

This line is entering 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 in it. 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 end up with 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 simply selects them.

  • 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.

input box for filter

  • Finally, click on OK.

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

select visible cells after using autofilter using excel vba


3. Select Visible Cells After Autofilter by Choosing Criteria with Input Box

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. And 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.

opening vba window

  • 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

🔎 Breakdown of the Code

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 are using 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 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 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 simply selects them. Also, use the same range as the previous one if you changed it to a bigger one.

  • 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.

inserting filter value

  • Then click on OK.

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

visible cells selected after autofilter using vba in excel

Read More: How to Copy Visible Cells Only without Header Using VBA


4. Select and Copy Visible Cells in New Sheet After 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

🔎 Breakdown of the Code

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

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

In the main sheet, the dataset will be autofiltered.

filtered result

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

copied result

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

Read More: How to Copy Visible Cells Only in Excel (4 Fast Ways)


Conclusion

So these were all the codes you can use to select visible cells after auto filter 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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo