Range as filter criteria

[Tablecloth98]

I'm trying to filter a column (E) by the values found in a range and to change all visible cells after that search to a new style ("Good"). For some reason it only seems to be picking up the first row in the range when applying the filter.

Does anyone know where I'm going wrong? (I've copied my code underneath)

Dim criteriaRange As Range

Dim criteriaArray As Variant

Set criteriaRange = Workbooks("Workbook.xlsm").Worksheets("Main")_
.Range("E2:E1048576").SpecialCells(xlCellTypeVisible)

criteriaArray = Application.Transpose(criteriaRange.Value)


ThisWorkbook.Sheets("Raw Data").Range("$A$1:$BA$1048576").AutoFilter Field:=5,_ Criteria1:=criteriaArray, Operator:=xlFilterValues, Criteria2:="<>"

ThisWorkbook.Sheets("Raw Data").Range("$A$2:$BA$1048576")_
.SpecialCells(xlCellTypeVisible).Style = "Good"

 

[Tablecloth98]

Made the mistake of asking ChatGPT how to fix this never again!

 

[Tablecloth98]

Solved it. It didn't like me using SpecialCells(xlCellTypeVisible) when I set criteriaRange

 

[Peter_SSs]

Solved it.

Good news. Thanks for letting us know.

Care to show us how you did that (it might be helpful for others)?

It didn't like me using SpecialCells(xlCellTypeVisible) when I set criteriaRange

The problem wasn't specifically that you used xlCellTypeVisible it was that, with a filter applied, those visible cells will likely be in a number of disjoint 'areas'. When you try to put them into an array as you did, only the first area gets put in. So if your filter did only have one continuous range (area) of visible cells there would not have been a problem with the use of xlCellTypeVisible.

 

[Tablecloth98]

That makes sense now that you say it. I used a bit of a workaround and copied the visible cells to a separate unfiltered sheet (putting them into a single range), then set criteriaRange equal to this new range.

It looks a little messy but gets the job done.

 

[Peter_SSs]

Thanks for the explanation.