Link to home
Start Free TrialLog in
Avatar of gregfthompson
gregfthompsonFlag for Australia

asked on

delete all rows in a worksheet except selected rows identified by an ID number

Worksheet named "Original"  contains thousands of rows, and in Column A there is an ID number. Each ID number is unique.

Worksheet named IDList contains a few hundred ID numbers.

The objective is to delete all rows in "Original" worksheet, that are different from the ID numbers in "IDList" worksheet.

Excel file is attached.
Original-and-IDList.xlsx
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image
Or you may try this code to delete all the rows from original sheet if the id's don't exit on the IDList sheet.
Please find the attached workbook where on Original sheet, you may click the button to delete the unwanted rows.

Sub DeleteRows()
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 3).End(xlUp).Row
Range("G4:G" & lr).Formula = "=MATCH(A4,IDList!A:A,0)"
On Error Resume Next
Range("G:G").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("G4:G" & lr).Clear
Application.ScreenUpdating = True
MsgBox "Rows deleted successfully.", vbInformation, "Done!"
End Sub

Open in new window

Original-and-IDList.xlsm
Avatar of gregfthompson
gregfthompson
Flag of Australia image

ASKER

Brilliant. Thanks.
Avatar of [ fanpages ]
[ fanpages ]
You're welcome.