Delete Entire column based on word search

[BradleyS]

I'm not very good at VBA and am struggling to work out a way to search across the top row column headers and delete the entire column if the following words do not exist in the relevant text description of that column.

The columns headers will have different text, but just in case it helps I only want to search the text held within the brackets.
The headers are written like this: A1 = "Example text (H12345)", B1 = "Example text (H12678)", C1 = "Example text (Y10678)"

My code is below but doesn't work. I only want to keep the column if: any text starts "H84*" or "Y01*" within the brackets. If there is no text like this (in the brackets) it needs to delete the column.

If searching text within brackets makes it over complicated, I'd just be happy with a text search of the cell contents.

<code>
Set MR = Range("A1:AG1")

For Each cell In MR
If cell.Value <> "H84*" And cell.Value <> "Y01*" Then cell.EntireColumn.Delete
Next
</code>

 

[Fluff]

Try it like

Dim rng As Range

For Each cell In MR
If Not cell.Value Like "*(H84*" And Not cell.Value Like "*(Y01*" Then
   If rng Is Nothing Then Set rng = cell Else Set rng = Union(rng, cell)
End If
Next
If Not rng Is Nothing Then rng.EntireColumn.Delete

 

[BradleyS]

That works perfectly. Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback