+ Reply to Thread
Results 1 to 2 of 2

Delete column based on header row value

Hybrid View

  1. #1
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    295

    Delete column based on header row value

    I found the following macro in another thread:

    Option Explicit
    Sub DeleteColumns()
    Dim a As Long
    Application.ScreenUpdating = False
    For a = 15 To 1 Step -1
      Select Case Cells(1, a).Value
        Case "ACTION", "PROVINCE", "TIME", "INTID"
          Cells(1, a).EntireColumn.Delete
      End Select
    Next a
    Application.ScreenUpdating = True
    End Sub
    assuming everything is apples to apples, how do you change "For a = 15 To 1 Step -1" to check all columns and not a specified amount?

    In other words one spreadsheet might have 15 columns whereas another might have 220.

    Thanks!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Delete column based on header row value

    Try this:
    Option Explicit
    
    Sub DeleteColumns()
    Dim a As Long
    Dim MaxCol As Long
    Application.ScreenUpdating = False
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = MaxCol To 1 Step -1
      Select Case Cells(1, a).Value
        Case "ACTION", "PROVINCE", "TIME", "INTID"
          Cells(1, a).EntireColumn.Delete
      End Select
    Next a
    Application.ScreenUpdating = True
    End Sub
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1