+ Reply to Thread
Results 1 to 5 of 5

Delete columns very slow

  1. #1
    Forum Contributor
    Join Date
    03-11-2010
    Location
    India
    MS-Off Ver
    2010
    Posts
    268

    Delete columns very slow

    Hi

    I have been using following code to delete columns in my sheet:-

    Please Login or Register  to view this content.
    This has suddenly started taking a lot of time.
    My sheet has about 35 columns (before deletion) and 3000 rows.

    What could be the reason for sudden drop in performance?
    What can I do to improve the performance of these delete statements?

    tia
    Ajay

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Delete columns very slow

    For general overview of how to best optimise your code see: http://blogs.msdn.com/excel/archive/...practices.aspx

    I think you will pick up a few pointers...

  3. #3
    Forum Contributor
    Join Date
    03-11-2010
    Location
    India
    MS-Off Ver
    2010
    Posts
    268

    Re: Delete columns very slow

    Hi DonkeyOte

    Thanks for responding. I went through the link. Thanks for that too.

    But, in present case, I think I have identified the reason (though not the root cause / solution yet) of sudden drop in performance.

    Here's the story:-
    I filter and copy data from another workbook (let's call it source) and paste on sheet "A" of my workbook. Then, I proceed to delete un-required columns from sheet "A".

    Now, I noticed that source sheet has been changed from excel 2003 to 2007, and ever since, the delete statements have become slow.

    Do I need to make any special changes to code during any of these steps when my source file changes from excel 2003 to 2007?
    a) while filtering the data on source
    b) while copying
    c) after pasting
    d) while deleting


    This is my code (stripped down):-

    Please Login or Register  to view this content.
    thanks
    Ajay

  4. #4
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: Delete columns very slow

    why don't you use these two commands before and after the deletion of columns respectively
    Please Login or Register  to view this content.
    rgds

    johnjohns

  5. #5
    Forum Contributor
    Join Date
    03-11-2010
    Location
    India
    MS-Off Ver
    2010
    Posts
    268

    Re: Delete columns very slow

    Hi johnjohns

    I added these lines at start of my procedure.

    Please Login or Register  to view this content.
    This didn't help much.

    I found the performance was slow due to filtering data in source sheet, before copying:-

    Please Login or Register  to view this content.
    When I removed this filter, and copy all rows, the paste as well as deletion of columns all become fast, as original.

    So, my probelm is resolved for now, but does this make sense? Why should a filter (that too, in source sheet) impact performance of deletion of columns (which is done after source sheet is closed)?

    Ajay

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1