Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA to filter

Posted on 2016-09-27
17
Medium Priority
?
103 Views
Last Modified: 2016-09-30
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
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
17 Comments
 
LVL 8

Expert Comment

by:Koen
ID: 41817346
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.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41817362
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
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41817388
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 total points
ID: 41817552
good one Rgonzo1971 as usual.  much simplified than the one i provided.  uses activesheet.used range so not limited to the range A$1:$N$72866

i reliased i put a wrong filter Field

so here is the correct one

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:=13, Criteria1:=arry, Operator:=xlFilterValues
    On Error GoTo 0
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41819205
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.
0
 

Author Comment

by:Jagwarman
ID: 41819219
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
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41820080
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

0
 

Author Comment

by:Jagwarman
ID: 41821186
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
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41821188
have you tried my last answer it filters out System Entries
0
 

Author Comment

by:Jagwarman
ID: 41821217
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.
0
 
LVL 8

Assisted Solution

by:Koen
Koen earned 500 total points
ID: 41821393
@Jagwarman,

My first link gives you the code to read your data column into an array (reading all the values you have in your sheet, no need to type them).
Then you apply the filter (as rgonzo instructs) leaving out the non wanted items
Arry = Filter(Arry, "System Entries", False)

done...
0
 

Author Comment

by:Jagwarman
ID: 41822975
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.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41823020
Could you send a dummy
0
 

Author Comment

by:Jagwarman
ID: 41823099
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
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1000 total points
ID: 41823234
then try (no need of the criteria sheet)
Sub Test()
Dim Arry

With Sheets("Sheet1")
    .Activate
    Set Rng = Range(.Range("M1"), .Cells(Rows.Count, "M").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:=13, Criteria1:=Arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41823539
perfect Rgonzo Many thanks.
0
 

Author Closing Comment

by:Jagwarman
ID: 41823860
Thank you to everyone but Rgonzo1971 was on the button.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question