Loop to Delete the Left and right Value if a Specific String is found in a Range

[Johnny Thunder]

Hi Group,

Need help on a macro that would be able to clear contents to the left and right of a found string.

My range is currently set at (I6:AI43), if any cell within that range contains the Text "Promo Ended" I need to clear the cell to the left and right as well as the found string.

My range can extend further down but not wider so I will also need to account for an actual LastRow type code instead of hardcoding the row 43 like my current range. The range will always start at row 6 though.

Any help is appreciated.

 

[Norie]

Try this.

Sub ClearPromo()
Dim cell as Range
Dim rng As Range

    Set rng = Range(" I6:AI43") 

    For Each cell In rng.Cells
        If cell.Value = "Promo Ended" Then cell.Offset(,-1).Resize(,3).Clear
    Next cell

End Sub

 

[Peter_SSs]

I would target the cells of interest more directly like this

Sub Clear_Cells()
  Dim lr As Long
  Dim rFound As Range
  
  lr = Columns("I:AI").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  With Range("I6:AI" & lr)
    Set rFound = .Find(What:="Promo Ended", LookIn:=xlValues)
    Do Until rFound Is Nothing
      rFound.Offset(, -1).Resize(, 3).ClearContents
      Set rFound = .Find(What:="Promo Ended", LookIn:=xlValues)
    Loop
  End With
End Sub

However, I really think there needs to be some clarification

1. Do you have data in columns H and/or AJ, can "Promo Ended" appear in columns I or AI and if yes to both, if "Promo Ended" is in one of those edge columns, should the adjacent cell outside the I:AI range be cleared? Both my code and Norie's will clear that 'outside' data and that may be an wanted result.
   
   
2. Could "Promo Ended appear in two adjacent cells like shown below? If so, I presume that both I6 and L6 should be cleared but both my code and Norie's will clear I6 but not L6.
   
   
3. It would also be good to confirm that the only text in the cells of interest is "Promo Ended" not something like "Promo Ended on Friday"

All issues above can be addressed, but we need to know if they are issues and if so what should happen.

Johnny Thunder 2020-07-21 1.xlsm
	I	J	K	L
6	data	Promo Ended	Promo Ended	data
Sheet1

 

[Johnny Thunder]

Thank you both for the help on this project. @Norie the solution worked flawlessly! Thank you.

 

[Peter_SSs]

Just confirming that points 1 & 2 in particular that I raised are not possible or not relevant?

 

[Johnny Thunder]

Peter, there are 7 section, each containing 3 Columns and a blank column in between. Each middle column in those sections contains a drop down with the potential for having "Promo Ended" if the code finds it, I needed the column to the left and right of the found value cleared as well as the found value. Hope that explains the need.

 

[Peter_SSs]

Hope that explains the need.

It does. It means 2 adjacent cells cannot have "Promo Ended" & it also means that "Promo Ended" cannot appear in one of the edge columns. Thanks for the clarification.