Solved Hello, I would like to find a way to search multiple entry.

August 6, 2016 at 10:55:06
Specs: Windows 10
I have a worksheet with about 800+ numbers (employee ID number) and I have a list of approx 200+ IDs which i want to delete from the large list.
Is there any way to search and remove or highlight all those 200+ numbers.

All numbers are in one worksheet.


See More: Hello, I would like to find a way to search multiple entry.

Report •

✔ Best Answer
August 7, 2016 at 18:29:37
Try this macro. I suggest that you try it in a backup copy of your workbook in case things go terribly wrong. This code will delete cells and deletions done by a macro can not be undone.

Sub WhoIsNotHere()
Dim last_srcRw As Long
Dim last_delRw As Long
Dim ID As Range
Dim del_ID As Range
'Determine last Rows with data in Columns A & C
 last_srcRw = Range("C" & Rows.Count).End(xlUp).Row
 last_delRw = Range("B" & Rows.Count).End(xlUp).Row
'Loop through Column C
  For Each ID In Range("C2:C" & last_srcRw)
   With Range("B2:B" & last_delRw)
'Delete cells in Columns A & B when value from C is found
    Set del_ID = .Find(ID, lookat:=xlWhole)
     If Not del_ID Is Nothing Then
      Range("A" & del_ID.Row & ":B" & del_ID.Row).Delete shift:=xlUp
     End If
   End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
August 6, 2016 at 11:10:39
Do you want to delete just the cell with the number or the entire row for that number?

If you want to delete the entire row, are the numbers from your 200+ list in those same rows? If so, can they be moved so that nothing in the 200+ list gets deleted when the row is deleted?

A little more info re: the layout of your sheet would be helpful.

(BTW...I am assuming that using a macro is OK. If so, the process is pretty easy.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 6, 2016 at 11:30:42
Hello,

There is one column with names and besides that there is column of ID number,
I want to delete the 200 entries from both of them. Name and ID.


Report •

#3
August 6, 2016 at 17:12:30
If you are looking to delete duplicate ID numbers
and all the ID data is in one column,
then take a look on the Ribbon,
under the Data tab,
and try Remove Duplicates

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
August 6, 2016 at 19:22:00
It would really help if you provided some more specific information so we can try to find a solution that fits your needs.

In what column is the list of 800+ IDs?
In what column are the names associated with those IDs?
In what column is the list of 200+ ID's?
Will there ever be more than one instance of the IDs that are to be deleted in the list of 800+? In other words, if 12345 is in your list of 200+, will 12345 be found more than once in the list of 800+?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 6, 2016 at 21:48:19
Here is the sheet.

In Column A2 to A584 there are Serial number

In Column B2 to B584 i have all the ID number of employees.

In Column C2 to C357 I have the list of employees that are present on Site.

Now i want to know the list of IDs who are not present. That is, the ID of which are missing from Column B but are present in Column C

message edited by Rual


Report •

#6
August 7, 2016 at 18:29:37
✔ Best Answer
Try this macro. I suggest that you try it in a backup copy of your workbook in case things go terribly wrong. This code will delete cells and deletions done by a macro can not be undone.

Sub WhoIsNotHere()
Dim last_srcRw As Long
Dim last_delRw As Long
Dim ID As Range
Dim del_ID As Range
'Determine last Rows with data in Columns A & C
 last_srcRw = Range("C" & Rows.Count).End(xlUp).Row
 last_delRw = Range("B" & Rows.Count).End(xlUp).Row
'Loop through Column C
  For Each ID In Range("C2:C" & last_srcRw)
   With Range("B2:B" & last_delRw)
'Delete cells in Columns A & B when value from C is found
    Set del_ID = .Find(ID, lookat:=xlWhole)
     If Not del_ID Is Nothing Then
      Range("A" & del_ID.Row & ":B" & del_ID.Row).Delete shift:=xlUp
     End If
   End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#7
August 7, 2016 at 19:30:30
Thank you. I will try this in couple of hours.

Report •

#8
August 7, 2016 at 20:05:14

Report •

Ask Question