Advance Filter, Find the Left-Overs

[seekeagle]

Assume that I have a database with 10 columns and that I know how to use an advance filter to copy selected lines to another worksheet based on the value in Column A. Assume that Column A is a list of names with Jane, Sue, ****, Harry, and about a dozen other people.

I would like to filter this table into five parts: (1) Jane, (2) Sue, (3) ****, (4) Harry, and (5) the Left-Overs. I know how to get lists 1 through 4 by using an advance filter. I also know how to use three rows of criteria to exclude 3 names. However, I do not know how to get List 5 where I need to exclude four (or more) names from the list.

I don't think this is possible directly on a worksheet, but I need to do it in VBA. Can anyone help? Is there a completely different approach to reach my objective? Your help will be appreciated.

 

[hiker95]

seekeagle,

You could copy your original data to a new sheet named "LeftOvers", and then delete all rows, where in column A are equal to Jane, Sue, ****, and Harry.

 

[hiker95]

seekeagle,

Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Title A	Title B	Title C	Title D	Title E	Title F	Title G	Title H	Title I	Title J
2	Jane
3	Sue
4	****
5	Harry
6	seekeagle
7	Jane
8	Sue
9	****
10	Harry
11	Joseph
12	Jane
13	Sue
14	****
15	Harry
16	Janet
17	Jane
18	Sue
19	****
20	Harry
21	David
22	Jane
23	Sue
24	****
25	Harry
26	Peter
27	Jane
28	Sue
29	****
30	Harry
31	Hiker95
32	Jane
33	Sue
34	****
35	Harry
36	AnotherName
37
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Title A	Title B	Title C	Title D	Title E	Title F	Title G	Title H	Title I	Title J
2	seekeagle
3	Joseph
4	Janet
5	David
6	Peter
7	Hiker95
8	AnotherName
9
LeftOvers

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT+Q to exit the Editor, and return to Excel

Option Explicit
Sub GetLeftOvers()
Dim LR As Long, a As Long
Dim NameArray() As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
NameArray = Array("Jane", "Sue", "****", "Harry")
Set ws1 = Sheets("Sheet1")
On Error Resume Next
Sheets("LeftOvers").Select
If Err Then
  Worksheets.Add(After:=ws1).Name = "LeftOvers"
  Set ws2 = Sheets("LeftOvers")
Else
  Set ws2 = Sheets("LeftOvers")
  ws2.Cells.ClearContents
End If
On Error GoTo 0
With ws1
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A1:J" & LR).Copy ws2.Range("A1")
  Application.CutCopyMode = False
End With
With ws2
  With .Range("A1:A" & LR)
    For a = LBound(NameArray) To UBound(NameArray)
      .AutoFilter
      .AutoFilter Field:=1, Criteria1:=NameArray(a), Operator:=xlAnd
      .Offset(1).Resize(LR).EntireRow.Delete
      .AutoFilter
    Next a
    .Columns.AutoFit
  End With
End With
End Sub

Then run the "GetLeftOvers" macro.

 

[seekeagle]

Hiker 95,

Thanks for your quick reply and coding. It looks really good, but it's late tonight, so I cannot try it until tomorrow. I will let you know how it works out. thanks again.

Seekeagle