VBA Filter Out (Exclude) Value by Input Box

[sethae]

I am trying to write some VBA in Excel 2007 to filter a list by excluding an item specified by the user in an input box, and then delete the remainder. I've done quite a bit of searching around, and have been able to figure out how to filter the list to the value in the input box, but I want the inverse of that (exclude the item in the input box):

Code:

Dim OfficeLocation As String

uiOfficeLocation = Application.InputBox ("Filter by Office Location", Type:=2)
If uiOfficeLocation = "False" Then Exit Sub: Rem Cancel pressed

Range("A1").Select
Selection.AutoFilter
Selection.Autofilter Field:=1, Criteria1:=uiOfficeLocation

/Code


The examples I've seen where people are trying to filter out a specific value consistently, rather than a user-input value, just add "<>" in front of their value, such as

Criteria1:="<>Main Street"

However this does not seem to work with the input box.

-Seth

 

[John Davis]

Just a guess, I'd try:

Criteria1:="<>" & uiOfficeLocation

 

[sethae]

Just a guess, I'd try:

Criteria1:="<>" & uiOfficeLocation

Yes, it really was that easy. Thanks very much for posting!

-Seth

 

[John Davis]

You're welcome. Glad too help out.