Remove selected data from a list

  • Thread starter Thread starter kingie
  • Start date Start date
K

kingie

i have a list of sequential numbers eg 1 to 100.
I have a second list of numbers eg 5,6,23,38,90
I want to remove the numbers in the second list from the first list.
Leaving me a list of numbers 1 to 100 minus the 5 numbers in the second list.
 
i have a list of sequential numbers eg 1 to 100.
I have a second list of numbers eg 5,6,23,38,90
I want to remove the numbers in the second list from the first list.
Leaving me a list of numbers 1 to 100 minus the 5 numbers in the second list.
Click to expand...

Hello,

I would suggest using the vlookup formula in the column next to the "1
to 100" column. Let's say that the "1 to 100" column is column A1 to
A100 and the "5 numbers" column is in "D1 to D5". The formula in
range B1 would look like : =VLOOKUP(A1;$D$1:$D5;false) . This formula
must be copied in column B from 1 to 100. This will return the value
if it's a common value or an error #N/A if not.
In a 2nd step you can also use the ISERROR function.

Hope this helps

new1@[no/spam]realce.net
 
This macro will do what you want. I assumed your 100 numbers are in Column
A, starting in A2, and your search numbers are in Column B starting in B2.
Post back if you need more. HTH Otto
Sub RemoveSome()
Dim rColA As Range
Dim rColB As Range
Dim i As Range
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColB
If Not rColA.Find(What:=i, LookAt:=xlWhole) Is Nothing Then
rColA.Find(What:=i, LookAt:=xlWhole).Delete Shift:=xlUp
End If
Next i
End Sub
 
Back
Top