+ Reply to Thread
Results 1 to 3 of 3

Insert procedure very slowly in 2010

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    3

    Insert procedure very slowly in 2010

    Hello!

    I am at a loss with an excel problem and really need your help.

    The following code is giving me a headache:
    For x = 12 To 18
    If TAB1.Cells(x, y) <> "" Then
    z = TAB1.Cells(x, y).Value
    Worksheets(Worksheets.Count).Columns(4).Insert
    TAB2.Columns(z).Copy Destination:=Worksheets(Worksheets.Count).Columns(4)
    boli = 1
    intcount = boli + intcount
    End If
    Next x
    In the cells (Tab1) are columnnumbers. These columnnumbers indicate what columns i copy from Tab2 into a new worksheet. I insert a new column in the new worksheet and copy the column from Tab2 into this column.

    This code is part of a big tool and running it on excel2007 in need approx 1 minute to make 100 new sheets with the columns i want. Doing it on excel 2010 i need about 4 HOURS!!!!

    After uncommenting the line
    Worksheets(Worksheets.Count).Columns(4).Insert
    excel2010 is as fast as 2007, so it must have something to do with this line.

    I already tried to find out what could help so what i found and tried is:
    application.calculation = xlmanual
    .entirecolumn
    i set the printer to document writer
    ActiveSheet.DisplayPageBreaks = False

    none of these approaches helped.

    I would really appreciate if one of you could help me!

    Ruediger

  2. #2
    Registered User
    Join Date
    11-27-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Insert procedure very slowly in 2010

    Nobody??? I am really desperate!

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Insert procedure very slowly in 2010

    [SOLVED]
    In my file, I had a fair amount of conditional formatting - like all the cells in one column in a worksheet that had 20,000 rows. And there were a few of those worksheets. In Excel 2010 (and 2007) conditional formatting seems to be saved as rules for ranges rather than on individual cells. When converting to Excel 2010, it created a new formatting rule for each conditionally formatted cell, i.e. many thousands of conditional formatting rules. When I deleted all the thousands of conditional rules and created one rule for each column, performance got back to what I would expect.

+ 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