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.


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.

Dataset

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.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

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

 excel vba autofilter copy visible rows in existing sheet

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.

 excel vba autofilter copy visible rows in existing sheet

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

VBA Code Copy Auto Filtered Visible Rows in New Sheet

This code is almost the same as the code for getting the output in the existing sheet. The only exception (yellow-colored in the above screenshot) is that you have to use the Worksheet Function to appoint the new working sheet (Sheet3 in the case of the practice workbook). Besides, the Range(“A1”) specifies that A1 will be the starting cell.

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

Copy Auto Filtered Visible Rows in New Sheet

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

excel vba autofilter copy visible rows Using Input Box

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.

 excel vba autofilter copy visible rows Using Input Box

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

 excel vba autofilter copy visible rows Using Input Box

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.

Checking Whether Auto Filter is Applied or Not

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.

Display All Filtered Data

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

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo