Using 'If' and 'ElseIf' function to identify if a cell holds a specific string,-CodePudding

I'm currently trying to create a code in VBA that identifies the string in Cell ("C" & (Int)), following which, if it has the value of "CHK," for example, it deletes the entire row. The following is the code I've put together which is not currently functioning as intended, in that no cells with the values specified are getting removed.

'''

    For i = 1 To Count Step 1

            If ws1.Range("C" & d).Value = "CHK" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "SOR" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "CAN" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "FBE" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "CHP" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "FER" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "FPE" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "SUN" Then

                 ws1.Range("C" & d).EntireRow.Delete

            ElseIf ws1.Range("C" & d).Value = "MAZ" Then

                 ws1.Range("C" & d).EntireRow.Delete

            End If

            d = d   1
    Next

'''

I've tried another method of referring to the rows, which did not help;

'''

    ws1.Rows(d).EntireRow.Delete

'''

Which is also not functional.

I'm uncertain whether it's my method of Checking the string value of the cell that's flawed, or my method of deletion. To give you an idea of the intended effect of the code I've attached two tables, one displays the raw data, and one displays the intended effect of the code.

I initially had a table inserted here through text, but the formatting stuffed up, so I attached an image of the table as it appeared to me. I'd be extremely appreciative of any help at all, Thanks.

CodePudding user response：

Try this. It goes from the bottom, to the top, so that deleted rows do not cause a row to be bypassed.

Option Explicit

Sub aaaa()
    Dim Count As Long, i As Long
    Dim rangeCell As String
    Const valuesToDelete As String = "CHK,SOR,CAN,FBE,CHP,FER,FPE,SUN,MAZ"
    Dim ws1 As Excel.Worksheet
    Set ws1 = ActiveSheet
    
    Count = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    
    For i = Count To 1 Step -1
        rangeCell = ws1.Cells(i, "C").Value
        If InStr(rangeCell, valuesToDelete) > 0 Then
            ws1.Rows(i).EntireRow.Delete
        End If
    Next i

End Sub

CodePudding user response：

This deletes rows for me when the strings are in Column C, although not all of them because the incrementation of d causes you to be targeting rows going up, and you need to be going through them backwards if you want to get them all, because when you delete one the rows get renumbered.

Assuming that Count here is the number of rows to check, and you just want to check them all, reference usedRange.Rows.Count directly and step from the end to the beginning:

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
    
For d = this.UsedRange.Rows.Count To 2 Step -1

  If ws1.Range("C" & d).Value = "CHK" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "SOR" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "CAN" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "FBE" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "CHP" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "FER" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "FPE" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "SUN" Then
      ws1.Range("C" & d).EntireRow.Delete
    ElseIf ws1.Range("C" & d).Value = "MAZ" Then
      ws1.Range("C" & d).EntireRow.Delete
    End If
            
 Next

and I think there are some ways to clean up that check. You can use a regex, an array or LIKE, any of which will let you make one call. Here are some implementations. First with a regular expression, which will let you check all of the conditions at once. I think specifying .Value is unnecessary so I'm just going to leave it out:

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Set parser = CreateObject("vbscript.regexp")
parser.Pattern = "^(CHK|SOR|CAN|FBE|CHP|FER|FPE|SUN|MAZ)$"

For d = ws1.UsedRange.Rows.Count To 2 Step -1
  
  If parser.test(ws1.Range("C" & d)) Then
    ws1.Range("C" & d).EntireRow.Delete
    End If
            
  Next

Now we've got one line of checking code and one line of deleting code to worry about trying to troubleshoot. I'm also going to use the Cells() object to specify instead of Range (easier mathwise) and I'll also use Rows().Delete instead of Range.EntireRow.Delete.

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Set parser = CreateObject("vbscript.regexp")
parser.Pattern = "^(CHK|SOR|CAN|FBE|CHP|FER|FPE|SUN|MAZ)$"

For d = ws1.UsedRange.Rows.Count To 2 Step -1
  
  If parser.test(ws1.Cells(d, 3)) Then
    ws1.Rows(d).Delete
    End If
            
  Next

Just a little easier to work with, I think. This works for me, it catches all the values, and it's going to be easier to troubleshoot because there's only a couple of lines to troubleshoot.

Now here's a version using like instead of the regular expression, you might find it simpler:

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Const vals As String = "|CHK|SOR|CAN|FBE|CHP|FER|FPE|SUN|MAZ|"

For d = ws1.UsedRange.Rows.Count To 2 Step -1
  
  If vals Like "*" & ws1.Cells(d, 3) & "*" Then
    ws1.Rows(d).Delete
    End If
            
  Next

or INSTR()

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Const vals As String = "|CHK|SOR|CAN|FBE|CHP|FER|FPE|SUN|MAZ|"

For d = ws1.UsedRange.Rows.Count To 2 Step -1
  
  If InStr(vals, ws1.Cells(d, 3)) Then
    ws1.Rows(d).Delete
    End If
            
  Next

Or you could use an array:

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Const vals As String = "CHK|SOR|CAN|FBE|CHP|FER|FPE|SUN|MAZ"
arr = Split(vals, "|")

For d = ws1.UsedRange.Rows.Count To 2 Step -1
  
  For Each a In arr
    If ws1.Cells(d, 3) = a Then
      ws1.Rows(d).Delete
      End If
    Next
  Next

This is slightly more complicated, but there are situations where looping through an array can be a little easier than trying to make text matches, for instance, if you had a list of numbers you want to check for.

Also, you can use filter on that array to return every element of the array that matches a certain value. If it doesn't, the UBOUND of the returned array is -1. If it returns a value (because the value in the cell matches one of the elements in the array), the UBOUND will be at least 0, so you can use that to make the check. BUT if the cell is empty passing the empty string to filter will make it return every element in the array, so you have to check that the cell value is not "" in your IF statement:

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Const vals As String = "CHK|SOR|CAN|FBE|CHP|FER|FPE|SUN|MAZ"
arr = Split(vals, "|")

For d = ws1.UsedRange.Rows.Count To 2 Step -1
  
  If UBound(Filter(arr, ws1.Cells(d, 3))) >= 0 And ws1.Cells(d, 3) <> "" Then
    ws1.Rows(d).Delete
    End If
  
  Next

CodePudding user response：

`If...Then...Else` vs `Select Case`

Dim drg As Range
Dim dCell As Range

For i = 1 To Count
    
    Set dCell = ws1.Range("C" & d)
    
    Select Case CStr(dCell.Value)
        Case "CHK", "SOR", "CAN", "FBE", "CHP", "FER", "FPE", "SUN", "MAZ"
            If drg Is Nothing Then
                Set drg = dCell
            Else
                Set drg = Union(drg, dCell)
            End If
    End Select
    
    d = d   1

Next

If drg Is Nothing Then Exit Sub

drg.EntireRow.Delete

If...Then...Else vs Select Case

`If...Then...Else` vs `Select Case`