Excel 2007: System Crashes Whilst Trying to Group Data...

BigJohnDiddy
BigJohnDiddy used Ask the Experts™
on
Hi Experts,
Please view the attached file. I've never come across this before! If I attempt to "Auto Outline" or "Group" data on this worksheet, Excel 2007 hangs so I need to end process using task manager.

in the worksheet attached, I wish to group the following rows:
a) 6 - 15
b) 18 - 52

and the following columns:
c) F - Q

Questions:
1) Can anyone else group the file?
2) Has anyone come across this issue before?
3) Are there any workarounds?

Thanks for your help!

John Diddy
Grouping-Issues.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
>>1) Can anyone else group the file?

As soon as I try to group rows 5 to 15, my Excel 2010 hangs ... There's probably something wrong with the file, though I can't figure out what.

Author

Commented:
Hi Teylyn,
I copied this worksheet from another spreadsheet, and it has exactly the same problems. I therefore assume that the problem isn't to do with the physical file itself.

However, I have absolutely no idea what formula I'm using that's causing Excel 2007 to crash out when I try to the outline tool!

Annoying!
Most Valuable Expert 2011
Top Expert 2011

Commented:
Try running this macro, then group:


Sub Fixit()
    With Range("A76:A" & Rows.Count)
        .RowHeight = 12.5
        .EntireRow.Hidden = True
    End With
End Sub

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Rorya,
The fix worked! Thanks for your help!!! Following on from your code, I realised that grouping will work if there are no hidden rows and columns on this sheet.

Can you please give me a bit of feedback as to why your macro works?

Cheers,

John Diddy
Most Valuable Expert 2011
Top Expert 2011
Commented:
It's not the hidden rows (if you notice, the code hides the rows again at the end) or columns. I think that the rows had had their height set to a very very small number, rather than actually being hidden (Format-Row-Unhide didn't work) and that was causing problems for some reason.

Author

Commented:
@ Rorya,
Thanks for that! I assumed hidden rows because at no point would I set multiple rows to a very, very, very small number! Do you have any idea where the shrunken rows come from in the first place?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Not a clue - some people do that deliberately so that you can't unhide them; others do it by accident. If it's your workbook, you would know better than I how it got that way! ;)
Most Valuable Expert 2011
Awarded 2010

Commented:
BigJohnDiddy said:

>> Do you have any idea where the shrunken rows come from in the first place?

Somebody selected all rows below row 75 and set the row height to zero. Who'd have thunk that it affects grouping?!?
Most Valuable Expert 2011
Awarded 2010

Commented:
You can re-set the rows to normal height manually, too. Click the last visible row header, then Ctrl-Shift-down, then click Format - Rows - Row Height - Autosize. Then hide them if you don't want to see them.
Most Valuable Expert 2011
Top Expert 2011

Commented:
FYI, setting the height to zero should not prevent you from unhiding the rows - it only works if you set it to a small positive number like 0.75.
Most Valuable Expert 2011
Awarded 2010

Commented:
Interesting! I opened the file and hit F5 - A88 and then went Format - Row - Row height and it showed 0 as the row height. Does that mean that the Row height dialog is using some kind of rounding?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yes - as far as I know it only goes in quarters.
Most Valuable Expert 2011
Awarded 2010

Commented:
Sonobaditch!
Most Valuable Expert 2011
Top Expert 2011

Commented:
Sohn des schlechten Krätze? :)
Most Valuable Expert 2011
Awarded 2010

Commented:
so ungefähr! :-)
Most Valuable Expert 2011
Top Expert 2011

Commented:
Was 'des' right?
Most Valuable Expert 2011
Awarded 2010

Commented:
Should have been 'der' since Krätze is feminin (go figure, 'der' for the feminine, ah well), but who understands Genitiv these days, anyway?

Genitiv ins Wasser! - Wieso? Ist denn das Wasser Dativ?

Most Valuable Expert 2011
Top Expert 2011

Commented:
And they say Germans have no sense of humour... 8^D
(I think it's funny, anyway)

Author

Commented:
Be wary of very, very small dimensions! Little buggers in every sense of the phrase! ')

Author

Commented:
Experts
To be honest, I have absolutely no reason to set the row heights to zero or 0.75 etc...!

So what could possibly have caused this to happen? All i do is hide rows!!!

John Diddy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial