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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I would target the cells of interest more directly like this

VBA Code:
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
IJKL
6dataPromo EndedPromo Endeddata
Sheet1
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
Thank you both for the help on this project. @Norie the solution worked flawlessly! Thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top