Deleting columns - faster vba code?

[CarlosTapas]

Hi (Long time reader first time poster)
As part of a Macro I export a range of cells that I calculate.
I export range A19:DW242 again and again so that it becomes a long dataset of about 70,000+ rows in a seperate sheet..
A then need to “clean” this data by deleting columns that are not required.
However this takes more time than I would like and would welcome some ideas on speeding this relatively simple task up.
The columns to be kept are identified in row 4 when the cell contains “report”
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sub deletecolumns()
Dim ginger As Integer
Dim pizza As Integer
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
<o></o>
Sheets("Output").Select
pizza = 1
For ginger = 1 To 160
If Cells(4, pizza).Value <> "Report" Then
Columns(pizza).Delete
Else
pizza = pizza + 1
End If
Next ginger
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
<o></o>
Application.ScreenUpdating = True
End Sub


Thank you for you assistance!

 

[Hermanito]

you name your counter variable pizza?? Weird

The rule of thumb is to work backwards when deleting rows or columns, in my experience that works faster, so change your loop to count backwards (use 'Step -1' at the end of the For line). Take care to not mess up the counter, often it's easier to work with a While/Wend.

 

[Weaver]

Have a look at post #4 in this thread.

Basically, it uses the find object to search for criteria, and aggregates the columns to be deleted, rather than iterating through 1 column at a time and so is much faster.

http://www.mrexcel.com/forum/showthread.php?t=581544

 

[CarlosTapas]

Thank you Weaver - your link was spot on!