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
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