Md. Abdul Kader
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.
Download Practice Workbook
3 Methods to AutoFilter and Copy Visible Rows through Excel VBA
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 filtered, 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
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
In the above code,
i. I declared CatSites which means Category of the Sites as a String data type.
ii. Then I specified Education as the input of CatSites.
iii. In the later stage, I assigned two tasks. In the first line, I specified AutoFilter for the range A1:E14 and fixed the value of the field as 2 and Criteria1 as CatSites.
iv. Immediately, I used SpecialCells to specify the cells and fixed xlCellTypeVisible as the cell type (the first argument) of the Special Cells. All are added by .Copy to copy visible cells only.
v. Later, I utilized PasteSpecial to get the copied cells. Before doing that I appointed Range (“G1”) to get the copied rows where G1 would be the starting cell.
vi. Thereafter, I assigned False to CutCopyMode for clearing the clipboard.
vii. Finally, I turned off the Auto Filter by assigning AutoFilterMode to the False.
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: [Fix]: AutoFilter Method of Range Class Failed (5 Solutions)
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 Autofilter Values Not Equal to a Certain Value with VBA in Excel
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
In this VBA code,
i. I declared firstly the PlatformsMode i.e. Platforms in the dataset as the String data type.
ii. Immediately, I assigned InputBox to the PlatformsMode for entering a mode of the platforms.
iii. Then I changed the field of the AutoFilter as 4 (Platforms located in Column D) and Criteria1 as PlatformsMode.
iv. Afterward, I specified Sheet5 (new working sheet) with the Worksheet function.
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: Macro to Copy and Paste from One Worksheet to Another (15 Methods)
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 Subii. 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 SubConclusion
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
- VBA Autofilter: Sort Smallest to Largest (3 Methods)
- Excel VBA: Remove AutoFilter If It Exists (7 Ways)
- How to Copy and Paste in Excel When Filter Is On (5 Methods)
- PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)
- How to Copy a Cell in Excel Using Formula(7 Methods)
- All Paste Options in Excel with Time Saving Shortcuts
- How to Copy Rows in Excel (7 Easy Ways)
Your thinking and attitude are charming and helpful to a learning person. Go ahead.
Dear Deka,
It’s nice to hear from you with such wonderful appreciation. I myself always try to represent Excel applications in a more user-friendly way. And we, the ExcelDemy team, are working as a one-stop Excel solution provider.
Thanks, and take care of yourself. Goodbye!
Md. Abdul Kader
Editor, ExcelDemy.