filter a list

[barrfly]

I have a list in a column with approx. 10,000 records - call these deal
IDS. I want to remove the occurence of several of these ids either
with an advanced filter or a quick subroutine.

my dealIDs that I want to have removed are in a list on another
worksheet. there are about 50 dealsIDS that occur 4 to 5 times in the
list.

Now, I know I can write a vlook up to identify these deal ids to remove
and I know that I can sort them out but the goal is to create a filter
that can read the deal ID list (which will change) and remove the
records with those ids on the other tab.

suggestions??

 

[Zack Barresse]

Well, I'd suggest a VBA routine to do this. Could get it in one fell-swoop.
If you want an example, we have to assume a few things first...

1) your data table is in 'Sheet1'
2) your deal ID's are in 'Sheet2' and in column A starting at row 2
3) your data table had single row headers
4) your data table extends from A1 to Dx (where x is a variable row number,
dependent upon column A's last row)
5) assumed column D is the extent of your table, can be adjusted as
necessary

Assuming this, you can perform these steps:

1) Hit Alt + F11, enter the Visual Basic Editor (VBE).
2) Hit Ctrl + R, open the Project Explorer.
3) Select Insert (menu) | Module.
4) Paste the below code in the module (right {white/blank} pane).
5) Hit Alt + Q, return to Excel.
6) Save before anything is done.
7) Hit Alt + F8, select DeleteDealID and select Run.

Code:


Option Explicit
Sub DeleteDealID()

Dim wsID As Worksheet, wsTable As Worksheet
Dim rngID As Range, rngLoop As Range
Dim LastRow As Long, i As Long
Dim strID As String

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set wsID = Sheets("Sheet2") 'set as desired
Set wsTable = Sheets("Sheet1") 'set as desired

With wsTable

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rngID = .Range("A2:A" & LastRow)

If .AutoFilterMode Then .Cells.AutoFilter

On Error Resume Next '(for SpecialCells)
For i = 2 To wsID.Cells(Rows.Count, "A").End(xlUp).Row Step 1
strID = wsID.Cells(i, "A").Text
.Range("A1:A" & LastRow).AutoFilter 1, strID
Intersect(rngID, rngID.SpecialCells(xlCellTypeVisible)).Delete
Next i

.Cells.AutoFilter

End With

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub



HTH