How to optimize column deletion in a large table of data
 

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

How to optimize column deletion in a large table of data
 

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

On 13 Okt., 22:21, TomChm wrote:
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

How to optimize column deletion in a large table of data
 

Tom
Maybe something like this:
Range("B:B,D:D,F:F").Delete
Just add the columns you want. HTH Otto
"TomChm" wrote in message
...
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

How to optimize column deletion in a large table of data
 

'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 wrote:

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

--

Dave Peterson

How to optimize column deletion in a large table of data
 

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



"TomChm" wrote:

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

How to optimize column deletion in a large table of data
 

I didn't try it in xl2007.

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



TomChm wrote:

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

"TomChm" wrote:

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


--

Dave Peterson