Delete rows from excel

March 27, 2009 at 16:11:21
Specs: Windows Vista
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


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?

See More: Delete rows from excel

Report •

March 27, 2009 at 16:44:27
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
End Sub

Report •

March 28, 2009 at 07:34:16
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.

Report •

Related Solutions

Ask Question