Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to optimize? large non-contig cell range for data validation l | Excel Discussion (Misc queries) | |||
How can I create one column (stacked) from a large table of data.. | Excel Worksheet Functions | |||
large data file problems - pivot table with vba | Excel Programming | |||
calculations in large data set and in pivot table | Excel Programming | |||
organizing a large amount of data into a table | Excel Programming |