Delete Rows based on criteria list

Microsoft Office excel 2007 visual basic...
August 12, 2010 at 08:19:50
Specs: Windows XP
I have a macro that currently scans column C for specific ID Numbers. If ID Number (123456789 for example) appears then it deletes the entire row. However, this requires me to manually enter the select.case ID Numbers in my code. I would like to have a list of ID Numbers on one worksheet. Then I owuld like the macro to use the numbers on the worksheet as the "select.cases". This way I do not have to manually edit the code every time another ID Number is added.

See More: Delete Rows based on criteria list

Report •

#1
August 12, 2010 at 08:25:30
the current code i have that requires manual editing is :

Sub Exempt_List()
Dim rngStart As Range
Dim rngEnd As Range
Dim n As Integer


'RED
Sheets("red").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select
    
    
Next
'GREEN
Sheets("green").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select
    
    
Next
'BLUE
Sheets("blue").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select
    
Next
'PURPLE
Sheets("purple").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select

Next
'ORANGE
Sheets("orange").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select
    
Next
'YELLOW
Sheets("yellow").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select
    
Next
'BROWN
Sheets("brown").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select
    
Next
'GOLD
Sheets("gold").Select
'set start of data in column C
Set rngStart = Range("C2")
'find end of data in column C
Set rngEnd = Range("C" & CStr(Application.Rows.Count)).End(xlUp)

'work from end of data as deleting rows alters row numbers
For n = rngEnd.Row To rngStart.Row Step -1
    
'look for ID in column C and delete
     Select Case Range("C" & CStr(n))
      Case 123456789, 987654321
    Range("C" & CStr(n)).EntireRow.Delete
    End Select

Next n
End Sub


Report •
Related Solutions


Ask Question