Results 1 to 3 of 3

using VBA to delete multiple columns at once

This is a discussion on using VBA to delete multiple columns at once within the Excel Questions forums, part of the Question Forums category; I have written code that identifies which columns on a tab need to be deleted. However, my macro currently deletes ...

  1. #1
    Board Regular
    Join Date
    Aug 2007
    Posts
    160

    Default using VBA to delete multiple columns at once

    I have written code that identifies which columns on a tab need to be deleted. However, my macro currently deletes each column at once, at this proves very slow since it is in a massive file. Since I have to do this across 21 tabs, it takes about an hour which is way too long. Assuming I know which columns to delete (say columns 1,3,5, for instance) is there a way I can delete them all at once? The columns will be non-adjacent usually.

    Below is my code for identifying which columns to delete (and deletes them at once). No need to peruse it; I'm just attaching it just in case. The code can handle a multiple tab selection as well. Thanks a million!

    Jared
    Code:
    Sub DeleteColumns()
    'works on a continuous multitab group
    'BEWARE: the activecell's row becomes the row whose values are evaluated
    Dim NumberOfTabs: NumberOfTabs = 0
    Dim CurColumn%, OrigRow%, CurrentTab%
    NumberOfTabs = ActiveWindow.SelectedSheets.Count
    OrigRow = ActiveCell.Row
    
    DeleteValue = InputBox("Reminder: Macro will delete ALL columns having specified value in ENTIRE row of active cell." & _
                            vbCrLf & vbCrLf & "What non-numeric value should trigger the column deletion?")
    
    ActiveSheet.Previous.Activate  'deselects the grouped tabs, assuming the current tab is the leftmost tab
    ActiveSheet.Next.Activate      'returns macro to original tab
    
    
    Do  'Loop across tabs
    CurColumn = 256
    
    
        Do                     'Loops within a tab, starting with the leftmost cell and continuing to the end
            If DeleteValue = Cells(OrigRow, CurColumn).Value Then Columns(CurColumn).Delete
            y = Cells(OrigRow, CurColumn).Value
            CurColumn = CurColumn - 1
        Loop Until CurColumn = 0
            
        CurrentTab = CurrentTab + 1
        If CurrentTab <> NumberOfTabs Then ActiveSheet.Next.Select  'advance if still more tabs in originally grouped range
    Loop Until CurrentTab = NumberOfTabs
        
    End Sub

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    2,477

    Default Re: using VBA to delete multiple columns at once

    Here a few things that will improve performance:

    1) Unless all of your worksheets occupy 256 columns, instead of:
    CurColumn = 256
    use:
    CurColumn = ActiveCell.SpecialCells(xlLastCell).Column

    2) Turn off screen updating while processing the deletions by inserting:
    Application.ScreenUpdating = False
    after you get DeleteValue and
    Application.ScreenUpdating = True
    just before End Sub

  3. #3
    Board Regular
    Join Date
    Aug 2007
    Posts
    160

    Default Re: using VBA to delete multiple columns at once

    Thanks for the tips.

    Jared

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com