Delete Entire Column Based on Cell Value

[arpanpanchal]

Hi Friends

I am using the below code to delete columns which contain specific value. However I have to run the same code multiple times to get the final result. Can you please advise what's wrong with this? Also, If I want to put 2 conditions one being "Gross" and other being "Semi-Gross", what will be the code?

Sub DeleteColumn()

Set MR = Range("A2:AV2")

For Each cell In MR
    If cell.Value = "Gross" Then cell.EntireColumn.Delete
Next
 
End Sub

Thanks a lot for your time and effort.

[6StringJazzer]

The For loop is going to get confused if you delete columns in the middle of the range it's iterating. Let's say you are checking column B, and you need to delete it. The For loop is going to go to column C next, except the original column C is now column B because you deleted column B. But the loop is now going to go to what is now column C, so the "new" column B is going to be skipped.

The standard practice for deleting things is to start from the end and work backwards, to avoid that kind of thing. I have NOT tested this code but would be happy to do so if you provide your file.

Dim Cell As Range ' Use Option Explicit and always declare your variables!
Dim i As Long
Dim MR As Range

Set MR = Range("A2:AV2")

For i = MR.Count To 1 Step -1

   Set Cell = MR(i)
   If Cell.Value = "Gross" Or Cell.Value = "Semi-Gross" Then Cell.EntireColumn.Delete

Next i

[arpanpanchal]

Thank you very much Jeff..I really appreciate your time and effort. The code you provided is the one I wanted. It works perfect. Thanks so much buddy.

[6StringJazzer]

Glad it worked! You're welcome, and thanks for the rep.