2 cells same row match declared string, delete shift cells up.

[jamada]

Hi, If on same row, and when Col"E"(5) AND Col"R"(18) BOTH equal MYSTRING, I would like the row/cellrange deleted and shiftedup... have tried many variation's with my limited skillset here, and am stuck. Can someone please assist, ThankYou!!


Dim Lastrow As Long
Dim i As Long
Dim MyString As String
MyString = "XXXxx XX:XX X"
Lastrow = Range("L" & Rows.Count).End(xlUp).Row

For i = Lastrow To 2 Step -1
If Cells(i, 5) = MyString And Cells(i, 18) = MyString Then
Range(Cells(i, 1), Cells(i, 21))..Delete Shift:=xlUp

End If

Next i

 

[ParamRay]

Can do it this way:

Public Sub DeleteMyWord()
' Run this one
  Const strMY_WORD = "XXXxx XX:XX X"
  Call DeleteRows(strMY_WORD, True)
End Sub

Private Sub DeleteRows(ByVal strFindWord As String, _
              Optional ByVal blnMatchCase As Boolean = False)
  
  Const strSHEET_NAME = "Sheet2" '<= set sheet name here
  Dim lngLastRow As Long
  Dim lngRowNum As Long
  
  With ThisWorkbook.Sheets(strSHEET_NAME)
    lngLastRow = .Range("L" & .Rows.Count).End(xlUp).Row
    For lngRowNum = lngLastRow To 2 Step -1
      If blnMatchCase Then
        If .Range("E" & lngRowNum).Value = strFindWord Then
          If .Range("R" & lngRowNum).Value = strFindWord Then
            .Range("A" & lngRowNum & ":U" & lngRowNum).Delete Shift:=xlUp
          End If
        End If
      Else
        If UCase(.Range("E" & lngRowNum).Value) = UCase(strFindWord) Then
          If UCase(.Range("R" & lngRowNum).Value) = UCase(strFindWord) Then
            .Range("A" & lngRowNum & ":U" & lngRowNum).Delete Shift:=xlUp
          End If
        End If
      End If
    Next lngRowNum
  End With
End Sub

 

[Swapnil Shah]

Your code look correct only thing which can cause trouble is use of ".." before delete and replace it with "." (Provided column L is duly filled If not replace that with either column "E" or column "R").

 

[jamada]

ParamRay, Thank You for your solution. Slightly (alot) above my comprehension LOL.
On the other hand your IF statements provided me with another way I'm more familiar with. Thank you though!

Swapnil, I deleted the extra . and still had difficulties.

Here is my end solution which works well for me!... Thank You Again.

Dim Lastrow As Long
Dim i As Long
Dim MyString As String
MyString = "XXXxx XX:XX X"
Lastrow = Range("L" & Rows.Count).End(xlUp).Row

For i = Lastrow To 2 Step -1
If Cells(i, 5) = MyString Then
If Cells(i, 18) = MyString Then
Range(Cells(i, 1), Cells(i, 21)).Delete Shift:=xlUp
Else
If Cells(i, 5) = MyString Then
Range(Cells(i, 1), Cells(i, 12)).Delete Shift:=xlUp
End If
End If
End If

If Cells(i, 18) = MyString Then
Range(Cells(i, 12), Cells(i, 21)).Delete Shift:=xlUp
End If
Next i