none
Excel VBA - Find and delete a columns with a certain header RRS feed

  • Question

  • I need to:

    1. Find a column with a certain header (or first cell)
    2. Highlight that whole column
    3. delete the column

    I have these lines so far, the parts in the bracket is what i need to complete the script

    Sub MarketoTemplate_Cleanup()

        Sheets("PASTE_HERE_FIRST").Activate
        Dim l As Long
        l = Application.WorksheetFunction.Match("-- IGNORE --", Range("A1:BB1"), 0)
     ''{I need code to activate the cell where --IGNORE-- is found}
       ActiveCell.EntireColumn.Select
    ''{I need code to delete this highlighted column}

    End Sub




    • Edited by Damon Fong Wednesday, July 31, 2013 6:48 PM
    Wednesday, July 31, 2013 6:27 PM

Answers

  • This should do it:

    Sub MarketoTemplate_Cleanup()
        Dim rng As Range
        With Worksheets("PASTE_HERE_FIRST").Range("A1:BB1")
            Set rng = Worksheets("PASTE_HERE_FIRST").Range("A1:BB1").Find(What:="-- IGNORE --", _
                LookAt:=xlWhole, MatchCase:=False)
            Do While Not rng Is Nothing
                rng.EntireColumn.Delete
                Set rng = .FindNext
            Loop
        End With
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Damon Fong Wednesday, August 7, 2013 5:12 PM
    Wednesday, July 31, 2013 8:24 PM

All replies

  • Try

    Sub MarketoTemplate_Cleanup()
        Dim rng As Range
        Set rng = Worksheets("PASTE_HERE_FIRST").Range("A1:BB1").Find(What:="-- IGNORE --", _
            LookAt:=xlWhole, MatchCase:=False)
        If Not rng Is Nothing Then
            rng.EntireColumn.Delete
        End If
    End Sub


    Regards, Hans Vogelaar

    Wednesday, July 31, 2013 7:03 PM
  • That worked! Thank you so much!

    I did the following to loop it 50 times which works but how do I go about looping this until there are no more IGNORE columns instead of just a set amount of times?

    Sub MarketoTemplate_Cleanup()
    Dim Z As Integer
    Z = 1
    Do
        Dim rng As Range
        Set rng = Worksheets("PASTE_HERE_FIRST").Range("A1:BB1").Find(What:="-- IGNORE --", _
            LookAt:=xlWhole, MatchCase:=False)
        If Not rng Is Nothing Then
            rng.EntireColumn.Delete
        End If
    Z = Z + 1
    Loop Until Z > 50
    End Sub


    Wednesday, July 31, 2013 7:56 PM
  • This should do it:

    Sub MarketoTemplate_Cleanup()
        Dim rng As Range
        With Worksheets("PASTE_HERE_FIRST").Range("A1:BB1")
            Set rng = Worksheets("PASTE_HERE_FIRST").Range("A1:BB1").Find(What:="-- IGNORE --", _
                LookAt:=xlWhole, MatchCase:=False)
            Do While Not rng Is Nothing
                rng.EntireColumn.Delete
                Set rng = .FindNext
            Loop
        End With
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Damon Fong Wednesday, August 7, 2013 5:12 PM
    Wednesday, July 31, 2013 8:24 PM
  • Thank you once again! Running it the first time, I can already see how much more efficient it was.
    Wednesday, July 31, 2013 8:39 PM