Macro To Filter Column Based On What I Want To Remain Visible

Mister_J

New Member
Joined
Dec 14, 2017
Messages
20
I’ve been looking into finding or creating a macro that can filter a column of categories that continuously grows. I always want to filter the same categories but when new ones are added I have to add the new ones to the list to be filtered out. So can I create a macro that will filter everything except the 3 or 4 categories I use in my department? I found some auto-filter macros but they don't seem to work right and I have spent a while surfing though these forums trying to find something similar but have had no luck. If you need any further info or would like me the clarify please feel free to ask, and any help would be appreciated. Thanks! - Jason

Example: (I only want to see the rows that have electrical and mechanical, regardless of how many other variables are in this column.)

GIS
Electrical
Mechanical

Project Support
Management Directive
And so on…
And so on…
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try this

VBA Code:
Sub Filter_Cat()
  Dim ary As Variant
  ary = Array("Electrical", "Mechanical", "Math", "Etc")
  Range("A1").AutoFilter 1, ary, xlFilterValues
End Sub
 
Upvote 0
Thank you very much! But how do I change which column it filters on. I tried changing the range but it still filters on column A. It does what I want want it to but if I could make it filter on the default column that would be nice.
 
Upvote 0
I have a follow up question, can I modify this code to do the inverse? Basically hide the ones I list in the code and show everything else?
 
Upvote 0
Try this

You have not told me which column you want to filter, so you already know the way...

VBA Code:
Sub Filter_Cat()
  Dim ary As Variant, a() As Variant, j As Variant, c As Range, n As Long
  ary = Array("Electrical", "Mechanical", "Math", "Etc")
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    j = Application.Match(c.Value, ary, 0)
    If IsError(j) Then
      ReDim Preserve a(n)
      a(n) = c.Value
      n = n + 1
    End If
  Next
  Range("A1").AutoFilter 1, a(), xlFilterValues
End Sub
 
Upvote 0
In that case it can be:

VBA Code:
Sub Filter_Cat()
  Dim ary As Variant, a() As Variant, j As Variant, c As Range, n As Long
  ary = Array("Electrical", "Mechanical", "Math", "Etc")
  For Each c In Range("D2", Range("D" & Rows.Count).End(3))
    j = Application.Match(c.Value, ary, 0)
    If IsError(j) Then
      ReDim Preserve a(n)
      a(n) = c.Value
      n = n + 1
    End If
  Next
  Range("D1").AutoFilter 1, a(), xlFilterValues
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top