2

I have been looking all over for a solution to this VBA autofiltering issue, any ideas are appreciated:

I have a static list of autofilter criteria in a named range "FslList" - which i have converted into one dimention array for autofiltering column 14 in a data worksheet:

   Dim FSLArray As Variant
        Dim rngFSL As Range
        Set rngFSL = RawData.Worksheets(1).Range("FslList")
        FSLArray = rngFSL.Value

        With NewBook.Worksheets(1)
          .Cells.AutoFilter Field:=14, Criteria1:=Application.Transpose(FSLArray), Operator:=xlFilterValues

Once i filter out the values from the array - i need to delete them

          With .AutoFilter.Range
            Set DeleteRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
          End With

          DeleteRange.EntireRow.Delete
          NewBook.Worksheets(1).AutoFilterMode = False
        End With

My issue is that my list of data is allways changing, and not all the values from FSLArray are in the column to be filtered. Thus the autofilter stops, once it encounters a criteria that is not on the list - and does not include any following criteria when filtering.

What i would like to do is for the autofilter to continue filtering using other array criteria, if one or more of the elements in the array, is not found amongst the data to be filtered.

EDIT: i have changed the data in my array from numbers (which it is) to letters - it works fine with letters now.

I have tried re-writing the code and define a named range as suggested:

Elements i have in the array (range C11:C14) are: 

Acc
9158
11958 (this one is not present in the list of data)
15938
15940

The named range "PODCustList" is defined as follows: 

=OFFSET(Acc,1,0,COUNTA(Settings!$C:$C)-1,1)

The code is the same:

Dim PODCustArray As Variant
Dim rngPODCust As Range
Set rngPODCust = RawData.Worksheets(1).Range("PODCustList")
PODCustArray = rngPODCust.Value

With Worksheets(1)
  .Cells.AutoFilter Field:=7, Criteria1:=Application.Transpose(PODCustArray), Operator:=xlFilterValues

What i get in return after filtering is only rows with "9158" element in them filtered.

SOLVED: I needed to run my array through this - Criteria1:=Split(Join(Application.Transpose(PODCustArray))) for autofilter to correctly interpret the data within as a string array.

Can i adapt my code, or do i need to use a different approach?

Thank you in advance,

Improve this question
3
  • i think you might need to use a different approach including loops :/ Commented Sep 18, 2013 at 9:56
  • Are you sure? it works for me... Commented Sep 18, 2013 at 10:35
  • One moment posting an answer Commented Sep 18, 2013 at 10:46

2 Answers 2

Reset to default
6

My issue is that my list of data is allways changing, and not all the values from FSLArray are in the column to be filtered. Thus the autofilter stops, once it encounters a criteria that is not on the list - and does not include any following criteria when filtering.

It depends on how have you defined your Range("FslList")

See this example

I have a workbook which has Sheet1 and Sheet5. Sheet1 has the list and Sheet5 has the data which needs to be filtered. The workbook can be downloaded from HERE

enter image description here

Now select A1 in Sheet1 and give it a name, say, Criterias. Next create a name called FslList in the Name Manager and set the formula as =OFFSET(Criterias,1,0,COUNTA(Sheet1!$A:$A)-1,1)

enter image description here

Now run this code

Option Explicit

Sub Sample()
    Dim FslList As Variant
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngCritList As Range, rngSh5 As Range

    Set ws1 = Worksheets("Sheet5")
    Set ws2 = Worksheets("Sheet1")

    Set rngSh5 = ws1.Range("$A$1").CurrentRegion
    Set rngCritList = ws2.Range("FslList")

    FslList = rngCritList.Value

    rngSh5.AutoFilter Field:=1, _
                      Criteria1:=Application.Transpose(FslList), _
                      Operator:=xlFilterValues
End Sub

You will see that the list gets filtered even when eee is there in the criteria list but not in the list that needs to be filtered.

This is how the Sheet5 gets filtered after you run the macro

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

i have tried re-writing my code, for an array containing letters - it works, but for some reason, when the array contains only number characters - it does not: i will add an edit to my post shortly - with examples of how i defined the lists.
I have edited my post, seems to me the only differenc enow is that you defined the range to be filtered as "Set rngSh5 = ws1.Range("$A$1").CurrentRegion" might this be the problem becouse i am simply using With Worksheets(1) and Field:=7?
Sorry just came in. You might want to post your answer and accept it so that it benefits other users?
Sorry, fairly new to this page - just posted my answer, i have a restriction and will only be able to accept it after 2 days - will do just that. Thanks for the tip.
@Mr_Oppenheimer I think Sid's answer answers your question and consider how much time it took him to work it out. It provides a great and generalized solution to your problem with an explanation. Consider accepting this as the answer and have your answer as additional one that specifically relates your problem. Im sure you can accept his answer right away :)
1

Issue solved at last. The code was fine as it was when using with strings containing letters. However I needed to run my array through this - Criteria1:=Split(Join(Application.Transpose(PODCustArray))) for autofilter to correctly interpret the data within as a string array.

Improve this answer

1 Comment

+ 1 for solving it on your own! :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.