Delete multiple Columns from a worksheet with same header

Sara Phd 1 Reputation point

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.
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.


0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 106.5K Reputation points

    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)
            End If
    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.

    0 comments No comments