using VBA to delete multiple columns at once

JaredSorensen

Board Regular
Joined
Aug 23, 2007
Messages
160
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top