In my vba code below I am trying to delete to specific colors from a row. Right now I would like to combine 2 if statements into 1 if statement. Right now my code below is working but is inefficient if more colors are added. Look for the if statements regarding blue and red for this problem.

Sub collapse_columns()
    Dim x As Integer
    For x = 1 To 4
        collapse_column x
    Next
End Sub


Sub collapse_column(column_number As Integer)

    Dim row As Long
    Dim s As Worksheet
    Dim last_row As Long
    Set s = ActiveSheet ' work on the active sheet
    'Set s = Worksheets("Sheet1") 'work on a specific sheet
    
    last_row = ActiveSheet.Cells(s.Rows.Count, column_number).End(xlUp).row
    
    For row = last_row To 1 Step -1
      If Cells(row, column_number).Value = "red" Then Cells(row, column_number).Delete xlUp
    Next


   For row = last_row To 1 Step -1
      If Cells(row, column_number).Value = "blue" Then Cells(row, column_number).Delete xlUp
    Next
    
    
End Sub

CodePudding user response：

When I have many possible values that can trigger the same code, I like to use a string to hold the values, then search the string to find a match as follows:

Sub collapse_column(column_number As Integer)

    Dim row As Long
    Dim s As Worksheet
    Dim last_row As Long
    Set s = ActiveSheet ' work on the active sheet
    'Set s = Worksheets("Sheet1") 'work on a specific sheet
    
    last_row = ActiveSheet.Cells(s.Rows.Count, column_number).End(xlUp).row
    
    Dim colors_to_delete As String
    colors_to_delete = ",red,blue," ' be sure to keep the leading and trailing commas
    
    For row = last_row To 1 Step -1
      If InStr(1, colors_to_delete, "," & Cells(row, column_number).Value & ",") > 0 Then Cells(row, column_number).Delete xlUp
    Next
    
End Sub