Deleting multiple columns based on cell contents

[miraj42]

Hi All,

Was directed here by someone after not being able to find a solution online. I have an Excel report that gets sent through to me every week and I always have to unmerge the top two rows so all headings are unmerged and then delete all columns containing the words "Ex VAT", "Inc VAT" "Front" and "COT" (these usually appear in row 2).

Tried having a go at making a VBA script on it but having a tough time getting my head around it. Any help/advice is appreciated. Let me know if you need anything else, first time posting

Thanks

miraj42

 

[tyija1995]

Re: Help deleting multiple columns based on cell contents

Hi,

I've wrote this code here assuming that those key words appear in Row 2:

Sub unmergeAndSort()
    Dim j As Integer
    Dim endColRow2 As Integer
    
    Rows("1:2").Select
        With Selection
            .UnMerge
            .HorizontalAlignment = xlLeft
        End With
        
    endColRow2 = Cells(2, Columns.Count).End(xlToLeft).Column
    For j = 1 To endColRow2
        If Cells(2, j).Value Like "*Ex VAT*" Then
            Columns(j).Delete Shift:=xlToLeft
        ElseIf Cells(2, j).Value Like "*Inc VAT*" Then
            Columns(j).Delete Shift:=xlToLeft
        ElseIf Cells(2, j).Value Like "*Front*" Then
            Columns(j).Delete Shift:=xlToLeft
        ElseIf Cells(2, j).Value Like "*COT*" Then
            Columns(j).Delete Shift:=xlToLeft
        Else
            'Do Nothing
        End If
    Next j
End Sub

 

[miraj42]

Re: Help deleting multiple columns based on cell contents

Thanks a lot for this tyija1995, did the job perfectly. Really appreciate it