none
deleting columns contain text RRS feed

  • Question

  • how do i write code to delete the column that contains certain words anywhere in row 1.  So I have 40 different words that could appear and I want to delete the column if these words appear and move all other columns to the left
    Saturday, February 27, 2016 3:00 AM

Answers

  • >>>how do i write code to delete the column that contains certain words anywhere in row 1.  So I have 40 different words that could appear and I want to delete the column if these words appear and move all other columns to the left

    According to your description, I suggest that you could follow James Cone's suggestion.
    How to create string Array:

    Sub DemoArray()
        Dim words(1 To 5) As String
        
        words(1) = "Lord of the Rings"
        words(2) = "Speed"
        words(3) = "Star Wars"
        words(4) = "The Godfather"
        words(5) = "Pulp Fiction"
        
        Debug.Print words(5)
    End Sub

    How to delete columns containing specific word:

    Application.DisplayAlerts = False
        With Worksheets("Sheet1")
        lcol = .UsedRange.Columns.Count
        For i = lcol To 1 Step -1
            If .Cells(1, i).Value = "Test" Then .Columns(i).Delete
            Next i
        End With
    Application.DisplayAlerts = True

    For more information, click here to refer about Range.Delete Method (Excel)

    • Marked as answer by David_JunFeng Saturday, March 5, 2016 3:18 PM
    Monday, February 29, 2016 6:08 AM

All replies

  • Re:  delete columns containing specific words in row 1

    1.  Create an array containing the 40 words.
    2.  Loop thru row one and compare the contents of each cell to the contents of the array.
    3.  Delete any column that has a match.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 1:02 PM
    Saturday, February 27, 2016 3:40 AM
  • >>>how do i write code to delete the column that contains certain words anywhere in row 1.  So I have 40 different words that could appear and I want to delete the column if these words appear and move all other columns to the left

    According to your description, I suggest that you could follow James Cone's suggestion.
    How to create string Array:

    Sub DemoArray()
        Dim words(1 To 5) As String
        
        words(1) = "Lord of the Rings"
        words(2) = "Speed"
        words(3) = "Star Wars"
        words(4) = "The Godfather"
        words(5) = "Pulp Fiction"
        
        Debug.Print words(5)
    End Sub

    How to delete columns containing specific word:

    Application.DisplayAlerts = False
        With Worksheets("Sheet1")
        lcol = .UsedRange.Columns.Count
        For i = lcol To 1 Step -1
            If .Cells(1, i).Value = "Test" Then .Columns(i).Delete
            Next i
        End With
    Application.DisplayAlerts = True

    For more information, click here to refer about Range.Delete Method (Excel)

    • Marked as answer by David_JunFeng Saturday, March 5, 2016 3:18 PM
    Monday, February 29, 2016 6:08 AM