I have a report with the column A as the client ID numbers all the way down the column different numbers in each row, and my boss gave me a list of client numbers he wants me to delete from the report, how do I delete the numbers from row A so that it delets all of the rows with the same numbers in other words if i have a id number my boss gave to me of 12345

and there is a row with

12345 and some other information

I want to delets that row period, but i have 50,000 to delete at one time and i dont want to do it one at a time is there a way?

Did you realize that your post was one long sentence? I was exhausted by the time I finished it! Feel free to throw a period in there every now and then. ;-) First, make a backup of your workbook in case things go terribly wrong.

Next, put the list of ID's the boss gave you in a different sheet - let's say Sheet2, Column A.

Now, let's assume the list you need delete rows from are in Sheet1, Column A.

Right-click the sheet tab for Sheet1 or Sheet2, choose View Code and paste this in. Run it and see what happens.

Sub DeleteIDRows() 'Determine last row in big list LastOrgRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'Determine last row in Boss's List LastBossRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row 'Loop through Boss's List For ChkNxt = 1 To LastBossRow 'Grab next ID ID = Sheets(2).Range("A" & ChkNxt) 'Look for ID on Sheet1 With Sheets(1).Range("A1:A" & LastOrgRow) Set c = .Find(ID, lookat:=xlPart) 'If it's found, delete the row If Not c Is Nothing Then c.EntireRow.Delete shift:=xlUp End With 'Loop Next End Sub

Thank you DerbyDad03 used the formula worked perfect, there were 10 sheets, but I just changed the numbers of the sheets in the formula and it worked like a charm. Sorry for that first long post, lol.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History