Find Values in 2 Cells on the Same Row & Clear Contents

[dgr]

Hello,
I'm using Excel 2013. I'm looking for a VB macro solution.

I want to find <b>Hello</b> in any cell and find <b>Good morning</b> in any other cell along the same row. Then I want to delete (clear contents) all values in all the cells in between <b>Hello</b> & <b>Good morning</b>. I want to loop through the entire used range of the worksheet & delete any such occurrence. <b>Hello</b> will always be on the left hand side of <b>Good morning</b>.

Explained in a different way:

Cell A3 contains <b>Hello</b>
Cell A4 - A6 could contain some text or might be blank
Cell A7 contains <b>Good morning</b>

Under such circumstances, the macro should delete (clear contents) everything in Cell A4 - A6.

Could you help me with the code please?

Thanks

 

[onlyadrafter]

Hello,

your description is a bit confusing as you state the data is in column A, but then say Hello is always to the left of good morning.
I have assumed the good morning is on the same row as Hello.

Does this suit your needs?

Sub HELLO_GOOD_MORNING()
    For MY_ROWS = 1 To ActiveSheet.UsedRange.Rows.Count
        For MY_COLS = 1 To ActiveSheet.UsedRange.Columns.Count
            If Cells(MY_ROWS, MY_COLS).Value = "Hello" Then
                For MY_CHECK_COL = MY_COLS + 1 To ActiveSheet.UsedRange.Columns.Count
                    If Cells(MY_ROWS, MY_CHECK_COL).Value = "Good morning" Then
                        Range(Cells(MY_ROWS, MY_COLS + 1), Cells(MY_ROWS, MY_CHECK_COL - 1)).ClearContents
                    End If
                Next MY_CHECK_COL
            End If
        Next MY_COLS
    Next MY_ROWS
End Sub

 

[Peter_SSs]

Can we assume there will never be more than one "Hello" and one "Good morning" per line?

If not, some more description of just what to do in that circumstance would be required.

If so, you could also try this option that does not need to loop through every cell.

Sub HelloGoodMorning()
  Dim H As Range, GM As Range
  Dim FirstAdr As String
 
  With ActiveSheet.UsedRange
    Set H = .Find(What:="Hello", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not H Is Nothing Then
      Application.ScreenUpdating = False
      FirstAdr = H.Address
      Do
        Set GM = H.EntireRow.Find(What:="Good morning", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not GM Is Nothing Then
          If GM.Column > H.Column + 1 Then
            Range(H.Offset(, 1), GM.Offset(, -1)).ClearContents
          End If
          Set GM = Nothing
        End If
        Set H = .Find(What:="Hello", After:=H, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
      Loop Until H.Address = FirstAdr
      Application.ScreenUpdating = True
    End If
  End With
End Sub

Two further comments to consider if they matter to you:

1. onlyadrafter's code is case sensitive so would not find "hello" if that is possible with your data. Mine would find "hello".

2. If "Hello" and "Good morning" are in adjacent cells, onlyadrafter's code will delete those cells, not what is between them, which in this case is nothing.

 

[dgr]

Thank you Peter. Your example worked fine. I merely edited all instances of xlWhole to xlPart to suit my needs. I appreciate your help. Thank you once again.

 

[Peter_SSs]

I merely edited all instances of xlWhole to xlPart

Ah, assumption on my part.
Anyway, glad it helped get you to the result.