How to optimize column deletion in a large table of data

[TomChm]

Given some very large .csv files (200,000+ rows x 200 columns),
how would one optimize the deletion of a series of disjoint columns.
For example, what would be the best way to delete the following columns:
column1, 3, 8, 9, 10, 23, 24, 67, 89, 95
from a table with 200,000 rows?

Currently, I read the .csv file into Excel,
create a table,
set
Application.ScreenUpdating = False
Application.DisplayAlerts = False
then I call
table.ListColumns(columnName).Delete
for each columnName I want to delete.
then set
Application.ScreenUpdating = True
Application.DisplayAlerts = True

However, .Delete is an expensive operation and takes about 15-20 seconds
to complete the deletion of each column. Is there a better way
to delete a series of disjoint columns?


Any help is appreciated.
Tom

 

[Per Jessen]

Hi

Maybe this will help you:

dim ColsToDelete as Range
Set ColsToDelete = Union(Columns(3), Columns(8), Columns(9))
Table.ListColumns(ColsToDelete).Delete

Regards,
Per

 

[Otto Moehrbach]

Tom
Maybe something like this:
Range("B:B,D,F:F").Delete
Just add the columns you want. HTH Otto

 

[Dave Peterson]

'columns 1, 3, 8, 9, 10, 23, 24, 67, 89, 95
ActiveSheet.Range("a1,c1,h1:j1,w1:x1,bo1,ck1,cq1").EntireColumn.Delete

Do this before you create the table.

 

[TomChm]

Thanks, Per, Otto and Dave for your quick replies.

@Per - I got "subscript out of bounds", when i tried using
Union on table listcolumns.

@Dave - Sounds like there isn't a way to this with the Table Object? I was
afraid
of this. I try this out later tonight.

Tom

 

[Dave Peterson]

I didn't try it in xl2007.

But it failed when I used xl2003's List (insert|List)