CODEWITHSUNDEEP
vbastringfor-loopif-statementvariables
Max
Max

Reputation: 3

search for multiple strings to delete within a excel worksheet

In my VBA code below it searches for any cell for red to delete. The line is at

Dim colors_to_delete As String: colors_to_delete = "red"

What would I add to this code so it delete red and blue?

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 = s.Cells(s.Rows.Count, column_number).End(xlUp).row

Dim colors_to_delete As String: colors_to_delete = "red"

For row = last_row To 1 Step -1

    If InStr(1, " " & s.Cells(row, column_number).Value & " ", " " & colors_to_delete & " ") > 0 Then
    
        s.Cells(row, column_number).Delete xlUp
        
    End If

Next row

End Sub

Upvotes: 0

Views: 78

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

You could use an array of color names:

Sub collapse_column(column_number As Integer)

    Dim row As Long
    Dim s As Worksheet
    Dim last_row As Long, clr, c as Range

    Set s = ActiveSheet ' work on the active sheet
    last_row = s.Cells(s.Rows.Count, column_number).End(xlUp).row
    
    For row = last_row To 1 Step -1
        Set c = s.Cells(row, column_number)
        For Each clr in Array("red", "blue")  '<< array to check against
            If InStr(1, " " & c.Value & " ", " " & clr & " ") > 0 Then
                c.Delete xlUp
                Exit For 'stop checking 
            End If
        Next clr
    Next row

End Sub

Upvotes: 1

Related Questions