+ Reply to Thread
Results 1 to 3 of 3

Delete multiple columns without specified cell content

Hybrid View

  1. #1
    Registered User
    Join Date
    03-22-2019
    Location
    Los Angeles
    MS-Off Ver
    2013
    Posts
    34

    Question Delete multiple columns without specified cell content

    My sheet has about 300 columns of data and I want to delete all columns that don't contain specific names in Row 2, i.e., TEST1, TEST2, TEST3, TEST4. This is not formated as a table.
    The code below "works" but seems to go into an infinite loop based on Debug.Print i. Without, i = i - 1, the code will complete without fully deleting columns, so I have to re-run multiple times.

    Sub cleanCl()
        Dim rng As Range
        Dim ccls as Integer
    
        Application.ScreenUpdating = False
    
        ccls = ActiveSheet.UsedRange.Columns.Count
        For i = 1 To ccls
    '        ccls = ActiveSheet.UsedRange.Columns.Count ' decrease loop count
            Set rng = Range(Cells(2, i).Address)
            If Not (rng Like "*TEST1" Or rng Like "*TEST2" Or rng Like "*TEST3" Or rng Like "*TEST4") Then
                rng.EntireColumn.Delete
                i = i - 1
            End If
            Debug.Print i
    
        Next
    
        Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,640

    Re: Delete multiple columns without specified cell content

    Try
    Sub test()
        Dim i As Long
        For i = Cells(2, Columns.Count).End(xlToLeft).Column To 1 Step -1
            If Not Cells(2, i).Value Like "*TEST[1-4]" Then Columns(i).Delete
        Next
    End Sub

  3. #3
    Registered User
    Join Date
    03-22-2019
    Location
    Los Angeles
    MS-Off Ver
    2013
    Posts
    34

    Re: Delete multiple columns without specified cell content

    Quote Originally Posted by jindon View Post
    Try
    Sub test()
        Dim i As Long
        For i = Cells(2, Columns.Count).End(xlToLeft).Column To 1 Step -1
            If Not Cells(2, i).Value Like "*TEST[1-4]" Then Columns(i).Delete
        Next
    End Sub
    Thanks for the help jindon. This did the trick.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to delete content from same range on multiple worksheets
    By J4G036 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-12-2019, 03:13 AM
  2. I need to mass delete or split text from columns with different content
    By juanchoja in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2018, 09:45 AM
  3. Getting cell content based on multiple columns of data (Vlookup? IndexMatch?)
    By markhooten in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-11-2017, 04:48 PM
  4. Formula to show content in a cell based on criteria in multiple columns
    By thesmallwonder in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-15-2015, 06:31 PM
  5. IF same CONTENT FOUND IN CELL A THEN DELETE CONTENT IN B,C or D
    By mecutemecute in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-11-2013, 01:16 AM
  6. [SOLVED] delete cell content when other cell's content is removed
    By Pretpik in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2012, 11:06 PM
  7. Delete & Move columns based on Heading content
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2007, 10:09 AM

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