How to Autofilter and Copy Visible Rows with Excel VBA

You can easily apply the auto filter in Excel except using VBA code. But copying the filtered visible rows in the existing or a new working sheet is a tiresome task. Fortunately, you might do the two tasks simultaneously while using the Excel VBA. In this tutorial, I’ll demonstrate 3 methods to autofilter and then copy the filtered visible rows in Excel VBA with proper explanation.

How to AutoFilter and Copy Visible Rows through Excel VBA:3 Methods

Let’s be introduced with today’s dataset (A1:E14 cell range) as shown in the following screenshot. Here, the Number of Visits for each website is provided along with the Name and Category of the Sites. Besides, the dates and mode of Platforms are also given. Now we need to apply autofilter and then copy the visible cells only.

Let’s dive into the methods.

1. AutoFilter and Copy Visible Rows in Existing Sheet Using Excel VBA

In the beginning method, you’ll see the process of auto-filtering and then copying only visible cells in the existing worksheet.

For example, if you want to return the copied visible rows only in the existing working sheet (G1 is the starting cell) after auto-filtering, you may use this method.

Step 01: Inserting a Module

Firstly, open a module by clicking Developer > Visual Basic.

Secondly, go to Insert > Module.

Step 02: Copying the VBA Code

Now, just copy the following code and the detailed explanation is available after the code. So, you can adjust the code for your dataset wherever necessary.

Sub Copy_AutoFiltered_VisibleRows()
'Declares CatSites i.e. Category of the Sites
Dim CatSites As String
'Education is the filtering criteria
CatSites = "Education"
'AutoFilter for a specific category of the sites which is in Column B
ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
ActiveSheet.Range("G1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

Step 03: Running the VBA Code

When you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get the following filtered and copied visible rows.

Read More: Copy and Paste Values to Next Empty Row with Excel VBA

2.  Copy Auto Filtered Visible Rows in New Sheet

Again, you may need to get the filtered and copied visible rows in a new working sheet. In such a situation you may use the following code in a newly created module.

Sub Copy_AutoFiltered_VisibleRows_NewSheet()
'Declares CatSites i.e. Category of the Sites
Dim CatSites As String
'Education is the filtering criteria
CatSites = "Education"
'AutoFilter for a specific category of the sites which is in Column B
ActiveSheet.Range("A1:E14").AutoFilter
ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

If you run the macro after copying the code, you’ll get the following output in the new Working Sheet3.

Read More: How to Copy Multiple Rows in Excel Using Macro

3. AutoFilter Using Input Box and Copy Filtered Visible Rows

Lastly, if you want to utilize the InputBox to enter a specific value, you may utilize the third method. For instance, I want to use InputBox to enter a mode of the platforms. That means if I insert Web as the mode of platforms, the macro will auto-filter the dataset based on the Web, and then it’ll return the copied visible cells only.

Sub Copy_AutoFiltered_VisibleRows_InputBox()
'Declares PlatformsMode i.e. the Mode of Platforms
Dim PlatformsMode As String
'Using InputBox to enter value
PlatformsMode = InputBox("Enter a mode of platforms")
'AutoFilter for a specific mode of platforms which is in Column D
ActiveSheet.Range("A1:E14").AutoFilter
ActiveSheet.Range("A1:E14").AutoFilter field:=4, Criteria1:=PlatformsMode
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet5").Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

Eventually, after running the code, you’ll get a dialog box where you need to input a mode of platforms.

Finally, you’ll get the following output after pressing OK.

Apart from these 3 useful methods, copying filtered visible cells excluding the header method might be necessary that I skipped.

Read More: Excel VBA: Copy Row If Cell Value Matches

Quick Notes

i. Checking Whether Auto Filter is Applied or Not

Furthermore, when you have to apply an AutoFilter, you may get puzzled whether the auto filter is applied or not in the active working sheet. So, you can use the following code to check that.

Sub Checking_AutoFilter()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "Auto Filter is Applied"
Else
MsgBox "Auto Filter is Applied"
End If
End Sub

ii. Display All Filtered Data 

Again, you may need to retrieve all data after filtering. In that case, you may utilize the following code to display all data.

Sub Display_Data()
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

Download Practice Workbook

Conclusion

This is how you might AutoFilter and copy visible rows in Excel through VBA sophisticatedly. I strongly believe this article and especially the explanation will be highly helpful for you. Anyway, if you have any queries or recommendations, please share them in the comments section.

Related Articles

• How to Paste From Clipboard to Excel Using VBA
• How to Use VBA PasteSpecial for Formulas and Formats in Excel
• How to Use VBA PasteSpecial to Keep Source Formatting in Excel
• How to Use VBA to Paste Values Only with No Formatting in Excel
• Excel VBA: Copy Cell Value and Paste to Another Cell
• Excel VBA to Copy Only Values to Destination
• VBA Paste Special to Copy Values and Formats in Excel