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.

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

Ask Your Question

Weekly Poll