Subtotals cause hang with large data

[9tanstaafl9]

I have a worksheet that uses Excel 2003's automatic subtotalling feature witSelectihin one of my macros. It works fine on my sample data, but when I try to run it on the larger set of customer data it hangs.

Any suggestions? I've been up all night, maybe this is just something really dumb, but I don't know. I stepped thru the macros, and it is hanging when I press f8 for the line that inserts the subtotals.

According to the task manager it really is hung, not just slow. Waited over 2 hours.

The line of code where it is hanging is:
Selection.subtotal groupby:=3, function:=xlsum, totallist:=array(14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=true, Pagebreaks:=false, Summarybelowdata:=true

I am a total newbie, so if there is a painfully obvious reason for why this is happening, don't think I'll be upset if you point out how dumb I am. Speaking of dumb, if this issue is posted elsewhere I apologize, I searched for hours before asking, but I may have looked in the wrong place.

 

[9tanstaafl9]

Oops, forgot the first part of your questioni. A window was popping up saying, "Microsoft Office Excel cannot determine which row or list in your selection contains column lables..." If you choose OK it uses the first row as lables, which is what I need to have happen, so I just told it not to display the alert.

Thanks again for your help.

What was popping up originally? Are you trying to add subtotals to a range returned from an external data source?

 

[9tanstaafl9]

I've noticed something strange. Every time it hangs, Excel SAVES the workbook, without getting my okay to do so.

Another odd but possibly relavant piece of the puzzle... I have a macro that toggles between filtering and unfiltering the data onthis worksheet after the macro that I posted is run. The odd part is that immediately after I run the macro (posted earlier) and run then run the filter toggle (which would turn the filtering on) it takes about 5 seconds to run, but if I push the toggle button two more times (turning it off and back on again) it runs in less than a second.

I'm not trying to get an answer to the timing question, if so I'd do that on another post. I'm just mentioning these odd-seeming things in case they might point out something obviously wrong.

Also, I just realized I shoud've used code tags in my posting earlier, and I apologize for being so annoying. I won't be annoying (in that particular way) any more.

Thank you again for any help. I can't believe how great the people on this board are.

 

[RoryA]

Do you have any sort of Autosave add-in or AutoRecover set to run periodically? If so, you might try disabling them temporarily to see if that makes a difference. Also try right-clicking the querytable (data returned form the external source), choose Data Range Properties and make sure that the option to refresh in the background is not checked. I suspect that the ActiveWorkbook.RefreshAll line in your "Everything" sub may be causing a background refresh, which means that the rest of your code is trying to run whilst the data is being updated from the data source. You might try commenting that line out to see if it helps.

 

[9tanstaafl9]

Thanks for your reply. I really thought you had something, but I turned off the autorecover, I don't have any add-ins besides Colos HTML one, and I turned off the Enable Automatic Refresh for the queries on all the worksheets (thanks for the detailed explanation of how to do that). Anyway, It made the macro that runs on the sample data take a minute and a half instead of 30 seconds. I'm going to try it on the real data, but I'm thinking this doesn't bode well...

I did notice while I was in there, that all 10 or so queries had the same really long query name. Now that I know where to make the change I'm going to try changing the names to shorter ones (as suggested below) and give each query a different name.

Thanks again for all your help. Aside from being frustrated that it works on some data but not other, I'm finding this whole process to be incredibly fun and the people here are great.

Do you have any sort of Autosave add-in or AutoRecover set to run periodically? If so, you might try disabling them temporarily to see if that makes a difference. Also try right-clicking the querytable (data returned form the external source), choose Data Range Properties and make sure that the option to refresh in the background is not checked. I suspect that the ActiveWorkbook.RefreshAll line in your "Everything" sub may be causing a background refresh, which means that the rest of your code is trying to run whilst the data is being updated from the data source. You might try commenting that line out to see if it helps.

 

[9tanstaafl9]

It WORKED!!!!

I unenabled the queries' automatic refresh, and I changed the names to unique names under 8 characters ad suggested. It's still slow (20 minutes to pull new data, 8 minutes to refresh old data), but at least it works.

Thanks again everyone for all your help! Now all I have to do is figure out how to get rid of the outline bar on the left, and or unprotect it so it's usable, but I'll post that on a new thread if I can't find an answer...

BTW, am I supposed to mark this thread as closed somehow?