How to Autofilter and Copy Visible Rows with Excel VBA

The dataset below has 5 columns displaying a Site’s name, Category, Date, Platform, and Number of Visits for each site.

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

Steps:

Open a module by clicking Developer > Visual Basic.

Go to Insert > Module.

Copy the following code:

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

Run the VBA Code

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

Method 2 – Copy Auto-Filtered Visible Rows in a New Sheet

You may need to get the filtered and copied visible rows in a new working sheet.

Use the following code in a newly created module in such a situation:

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

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.

Method 3 – AutoFilter Using Input Box and Copy Filtered Visible Rows

If you want to utilize the InputBox to enter a specific value, utilize the third method.

Use InputBox to enter a mode of the 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 the PlatformsMode, i.e., Platforms in the dataset, as the String data type.

ii. I assigned InputBox to the PlatformsMode for entering a mode of the platforms.

iii. I changed the field of the AutoFilter to 4 (Platforms located in Column D) and Criteria1 as PlatformsMode.

iv. I specified Sheet5 (new working sheet) with the Worksheet function.

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

You will get the following output after pressing OK.

Quick Notes

i. Checking Whether Auto Filter is Applied or Not

When you have to apply an AutoFilter, you may be unclear about whether the auto filter is applied or not in the active working sheet. Use the following code to check.

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

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 the Practice Workbook

AutoFilter and Copy Visible Rows.xlsm

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

Malin Deka Oct 3, 2022 at 6:26 PM

Your thinking and attitude are charming and helpful to a learning person. Go ahead.
- Reply
  Md. Abdul Kader Oct 4, 2022 at 11:26 AM
  
  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.
  - Reply
    fredrick aringo Apr 16, 2024 at 4:08 AM
    
    Thanks for a well explained scripts on using autofilter. The only thing I am not getting properly is the two vba lines below. Is it possible to just use the last one to avoid the repetation.
    ActiveSheet.Range(“A1:E14”).AutoFilter
    ActiveSheet.Range(“A1:E14”).AutoFilter field:=2, Criteria1:=CatSites
    - Shamima Sultana Apr 16, 2024 at 11:28 AM
      
      Hello Fredrick Aringo,
      
      You must use the last line to do AutoFilter based on criteria.
      
      ActiveSheet.Range(“A1:E14”).AutoFilter field:=2, Criteria1:=CatSites
      
      Please avoid the first line. We updated our code.
      
      Regards
      ExcelDemy