Excel Advanced Filter Query

  Ben Avery 11:44 03 Nov 2003


I have a quick question regarding an advanced filter on excel.

I have managed to set a filter which picks out the words in a column which I want using an advanced filter by typing the words into different rows in the criteria range. This works fine, but how do I change it to EXCLUDE these words?


Criteria range example:

Cell A1 = Forename Cell A2 = Surname

Cell B1 = BLANK> Cell B2 = Smith

Cell C1 = BLANK> Cell C2 = Jones


This sorts out all of the Smith & Jones' (no pun intended!) but how do I run the query excluding Smith & Jones' from the selection?


  graham√ 12:32 03 Nov 2003

The auto filter will do that. In the drop down menu select Custom and enter the parameters.

  Ben Avery 12:41 03 Nov 2003

Autofilter only performs one operation at a time in each column doesn't it? How can you set autofilter to do what I need?


  Ben Avery 12:45 03 Nov 2003

I meant it only performs 2 operations at a time. What would be the equivilent formula to type into the Advanced filter to perform the operations the custom filter will do for you?


  VoG II 12:51 03 Nov 2003

Can't you use

Cell B2 <> Smith

  Ben Avery 13:07 03 Nov 2003

Doesn't seem to work. The above was an example only, it's too much to go into what is actually on the sheet I'm doing but basically I have the advanced filter set up how you showed me quite some time ago.

That works really well.

I can use the advanced filter to search for several names at once by typing them in the same criteria range column but on seperate rows and adjusting the criteria range accordingly. There is no limit to how many names I can search for this way, but I can only only seem to search for names which I want to KEEP IN the filter not names I want to EXTRACT FROM the filter.

That's where my query lies.

If I have a list of surnames in column B, what do I type in the criteria range cells (say cells B2, B3, B4 & B5) to EXCLUDE "Smith", "Jones", "Brown" and "McDonald"?

By typing the words in on their own, I can remove the other names but I want to do the opposite without having to typ all the other words in the list in.

Am I making sense? Or is this dull, confusing and frankly less interesting than an in-flight magazine produced by Air Belgium??? ;o)


  VoG II 17:34 03 Nov 2003

I think that you need to change the criteria range cells to

<> "Smith" etc.

I understand what you want to do but not how, never having done this myself.

You could also use a little VBA routine to achieve what you want. The following code should be placed in a module.

It assumes that you have entered the names that you want excluded from your filter in Row 1 from Column A across. It further assumes that your names are in a list in Column A starting from Row 2.

Amend as required to achieve your objective


Sub HideNames()

Dim iCol As Byte

Dim iRow As Integer

iRow = 2

Do Until Cells(iRow, 1) = ""

iCol = 1

Do Until Cells(1, iCol) = ""

If Cells(iRow, 1) = Cells(1, iCol) Then


Selection.EntireRow.Hidden = True

Exit Do

End If

iCol = iCol + 1


iRow = iRow + 1


End Sub



  VoG II 23:13 03 Nov 2003

Very neat!

Just to be a pedant, you could find the last row using

xRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

  powerless 23:16 03 Nov 2003

Somebody has met his match.

  VoG II 23:21 03 Nov 2003

I didn't realise it was a competition.

Any regular knows that Whisperer is better than me at Excel but - as his name suggests - he only appears when I run out of ideas.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Moto G6 Play Review: First Look

iPad 9.7in (2018) review

Les meilleures coques pour iPhone 8 & iPhone 8 Plus