Delete multiple Columns from a worksheet with same header

Question

Hi there - I want to delete multiple columns from my excel sheet. the columns have same heading "Date" and they are in the sheet with the alternate position.
like
Column 1: Firm Name
Column 2: Date
Column 3: Return(Jan95)
Column 4: Date
Column 5: Return(Feb95)
Column 6: Date
Column 7: Return(Mar95)
.......Until Dec 2020

Please advise how I can remove these date columns as I need only return to analyse.

Regards,
Sara

Answer 1

Try a macro like this:

Sub DeleteDateColumnsFromActiveSheet()

    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim r As Range
    Set r = Application.Intersect(ws.Rows(1).Cells, ws.UsedRange)

    Dim i As Integer
    For i = r.Columns.Count To 1 Step -1

        Dim h As Range
        Set h = r.Cells(1, i)

        If h.Value = "Date" Then
            Dim c As Range
            Set c = ws.Columns(h.Column)
            c.Delete
        End If

    Next

End Sub

It assumes that the first row contains the header.

Because the operation cannot be undone, make some backup copies of Excel files before designing and trying your code.