VBA Filter

[helpexcel]

Hi,

Is there a way to define the Criteria1:=Array so that it shows all items except the ones you define? So how would i define the array if i wanted to show all values except Apple, Oranges, Pears?

Thanks!

 

[Fluff]

It should be

Set FilterRange = Range("A2[COLOR=#ff0000]:A[/COLOR]" & lastRow2)

 

[helpexcel]

DOH! Thanks again!! I forgot that a range needs a starting cell, then the column and last row.
How come .show doesn't work on the last code? wondering how i would change the code to show the values in the array instead of hiding them.

 

[Fluff]

There is no such thing as .Show for a dictionary.
What the code is doing is adding all unique values from col B into a dictionary & then removing the values from your "list".
The resulting array is then used to filter the data for everything but the values in your list

 

[helpexcel]

how would you do the opposite? so instead of filtering out everything in the list, it would filter (show) everything in the list?

 

[Fluff]

Simply plug the list into the filter

 

[helpexcel]

@Fluff are you able to help interpret how exactly this works? I love using it, but i really don't know how it works and how i would manipulate it to work in other scenarios?

Set ws3= ThisWorkbook.Worksheets("Sheet3")
Set ws1 = ThisWorkbook.Worksheets("Sheet1")


Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant


With ws1
lastrow = .Cells(Rows.Count, "G").End(xlUp).Row
inarr = Range(.Cells(3, 1), .Cells(lastrow, 18))
End With


With ws3
lastrow2 = .Cells(Rows.Count, "G").End(xlUp).Row
searcharr = Range(.Cells(3, 7), .Cells(lastrow2, 7))
outarr = Range(.Cells(3, 1), .Cells(lastrow2, 18))
End With


On Error Resume Next
For i = 3 To lastrow2
For j = 3 To lastrow
Searchfor = searcharr(i, 1)
If inarr(j, 1) = Searchfor Then
For kk = 1 To 18
outarr(i, kk - 1) = inarr(j, kk)
Next kk
Exit For
End If
Next j
Next i


With ws1
Range(.Cells(3, 1), .Cells(lastrow2, 18)) = outarr
End With

 

[Fluff]

As this is a totally different question, please start a new thread.

 

[helpexcel]

@Fluff I've been playing around this this code and i'm still stuck with "Simply plug the list into the filter". What does that mean?


Also, if i have multiple lists and want to use a validation drop down to determine which list to compare how would i make "list" below dependent on the entry. I have defined all of the ranges, just not sure how to use them.

For Each cl In "LIST"

Thanks!!

 

[Fluff]

"Simply plug the list into the filter". What does that mean?

If you have an array of Apples, Pears oranges.You use that array in the autofilter.
As for the rest of your question. Please start a new thread, as this is different from the OP

 

[helpexcel]

Sorry for being a little slow on the uptake here, but I don't know which array to put in the autofilter.

This currently compares List A with List B and removes the List B items from List A. I would like to remove the items not on List B from List A. I tried swapping them in this code, but it didn't work.

Thanks!

With CreateObject("scripting.dictionary")
For Each cl In ListA
.Item(cl.Value) = Empty
Next cl
For Each cl In ListB
If .exists(cl.Value) Then .Remove cl.Value
Next cl
ListA.AutoFilter 1, .keys, xlFilterValues
End With