Jagwarman

asked on 9/27/2016

VBA to filter

When recording a macro where you want to filter it would come out like the below so I presume that is static and it would always be looking for the same filter every time you run it

Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$65967").AutoFilter Field:=13, Criteria1:=Array( _
"Adam", "Carl ", "Dave", "Debbie", "Jack" _
, "James", "Joanna", "Kyle", "Lewis", "Molly", _
xlFilterValues

My problem is each time I would need to run it the names will not be the same and the range will also be different.

Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$72866").AutoFilter Field:=13, Criteria1:=Array( _
"Adam", "Carl ", "Dave", "Debbie", "Jack" _
, "James", "Lewis", "Molly", _
"Rebecca", "Ryan", "Sylvia"), Operator:= _
xlFilterValues

All I really want to do every time I run it is to say exclude from the filter “System Entries” and make sure the range covers all rows.

Could an expert provide me with the VBA code that will do this.

Many thanks

Microsoft ExcelMicrosoft Access

Last Comment

Jagwarman

8/22/2022 - Mon

Koen

9/27/2016

here is explained how you turn your column into an array you can use:

http://www.thespreadsheetguru.com/the-code-vault/2014/6/19/filter-data-with-an-excel-table-read-into-a-vba-array

then you find and remove the value you don't need
then you apply this array to your filter.

Professor J

9/27/2016

pass the array of criteria to a range and then your range will be holiding the criterias

like this. see attached example file.

Sub Test()
    Dim N As Long, r As Range
With Sheets("CriteriaSheet")
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        ReDim arry(1 To N)
        For i = 1 To N
            arry(i) = .Cells(i, 1)
        Next i
End With
On Error Resume Next
    Range("$A$1:$N$72866").AutoFilter
    ActiveSheet.Range("$A$1:$N$72866").AutoFilter Field:=2, Criteria1:=arry, Operator:=xlFilterValues
    On Error GoTo 0
End Sub

Open in new window

EE.xlsm

Rgonzo1971

9/27/2016

HI,

pls try

Sub Test()
Dim Arry

With Sheets("CriteriaSheet")
    Set Rng = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
    Arry = WorksheetFunction.Index(WorksheetFunction.Transpose(Rng), 1, 0)
End With
On Error Resume Next
    Set rngFilter = ActiveSheet.UsedRange
    rngFilter.AutoFilter
    rngFilter.AutoFilter Field:=13, Criteria1:=Arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub

Open in new window

Regards

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

SOLUTION

Professor J

9/27/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Jagwarman

9/27/2016

ASKER

Rgonzo1971 I get an error "Unable to get the index property of the worksheetFunction Class" on the line

Arry = WorksheetFunction.Index(WorksheetFunction.Transpose(Rng), 1, 0)

ProfessorJimJam your last one does filter.

However, my problem is, as I stated at the beginning " every time I run it is to say exclude from the filter “System Entries” " I never know what other names will be in the list but I do know I never want to include "System Entries" so my question is, is what I want to do not possible.

Jagwarman

9/27/2016

ASKER

Rgonzo1971 just realized that in your solution there needs to be more than one item in the CriteriaSheet. However, my problem still remains I need the reverse of what you are doing in that I only want to filter 'Out'
“System Entries”

Thanks

Rgonzo1971

9/28/2016

then try

Sub Test()
Dim Arry

With Sheets("CriteriaSheet")
    Set Rng = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
    If Rng.Cells.Count > 1 Then
        Arry = WorksheetFunction.Index(WorksheetFunction.Transpose(Rng), 1, 0)
    Else
        Arry = Array(Rng.Value)
    End If
End With
Arry = Filter(Arry, "System Entries", False)
On Error Resume Next
    Set rngFilter = ActiveSheet.UsedRange
    rngFilter.AutoFilter
    rngFilter.AutoFilter Field:=1, Criteria1:=Arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub

Open in new window

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Jagwarman

9/28/2016

ASKER

I guess the answer is I would always have to put the names on the criteria sheet [which is what I was hoping not to have to do] because if a new name is in the file it will not be picked up unless it is on the list. is it not possible for the criteria sheet to have the names that should not be in the filter instead of those that are in the filter

Rgonzo1971

9/28/2016

have you tried my last answer it filters out System Entries

Jagwarman

9/29/2016

ASKER

Hi Rgonzo1971 I have but so far as I can tell I still need to put the names I want to keep in the criteria sheet which is what I was hoping I would not need to do.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

SOLUTION

Koen

9/29/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Jagwarman

9/29/2016

ASKER

Koen, not being a VBA expert I'm not sure I fully understand. I presume I have to copy the script into my Macro. I made 'Table1' in my file as I presumed the macro would look for that

but when I run it it gets stuck at

"Set myTable = Settings_sht.ListObjects("Table1")"

with Subscript out of range.

Rgonzo1971

9/30/2016

Could you send a dummy

Jagwarman

9/30/2016

ASKER

A dummy copy as requested.

Using the criteria sheet with the names the macro works. However each day new people post entries and these are never captured because their names are not on the criteria sheet.
Filter.xlsm

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

Rgonzo1971

9/30/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER