Hiding sheets - Impact on Speed

[Timmy Mac1]

I've goneback to working on a spreadsheet recently where I had several sheets hidden. After unhiding some of these I noticed that the calculation (on automatic) was a lot slower after doing this.

I'm surprised that this has such an impact. Does anyone know if it actually is a feature of excel?

also, is there anyway of releasing all hidden sheets in one go or can it only be done one by one?

many thanks

tm

[Jim Rech]

I've never heard of the visible state of worksheets affecting calc speed.

You need a macro to unhide worksheets all at once:

Sub UnhideSheets()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Visible = xlSheetVisible
Next
End Sub


--
Jim
"Timmy Mac1" <[email protected]> wrote
in message news:[email protected]...
|
| I've goneback to working on a spreadsheet recently where I had several
| sheets hidden. After unhiding some of these I noticed that the
| calculation (on automatic) was a lot slower after doing this.
|
| I'm surprised that this has such an impact. Does anyone know if it
| actually is a feature of excel?
|
| also, is there anyway of releasing all hidden sheets in one go or can
| it only be done one by one?
|
| many thanks
|
| tm
|
|
| --
| Timmy Mac1
| ------------------------------------------------------------------------
| Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
| View this thread: http://www.excelforum.com/showthread...hreadid=492996
|

[Niek Otten]

< I've never heard of the visible state of worksheets affecting calc speed>

Well, there are some effects, although I don't know about this one.

If you have a large worksheet, like a 40 year financial projection with one
row per month, and you split the window to be able to see the start line
together with the bottom line, it takes almost
twice as long to calculate as without the split.
It seems as if Excel thinks it has to format all the rows for display
instead of just one screeenful.

--
Kind regards,

Niek Otten

"Jim Rech" <[email protected]> wrote in message
news:eKcT%23x%[email protected]...
> I've never heard of the visible state of worksheets affecting calc speed.
>
> You need a macro to unhide worksheets all at once:
>
> Sub UnhideSheets()
> Dim WS As Worksheet
> For Each WS In Worksheets
> WS.Visible = xlSheetVisible
> Next
> End Sub
>
>
> --
> Jim
> "Timmy Mac1" <[email protected]>
> wrote
> in message news:[email protected]...
> |
> | I've goneback to working on a spreadsheet recently where I had several
> | sheets hidden. After unhiding some of these I noticed that the
> | calculation (on automatic) was a lot slower after doing this.
> |
> | I'm surprised that this has such an impact. Does anyone know if it
> | actually is a feature of excel?
> |
> | also, is there anyway of releasing all hidden sheets in one go or can
> | it only be done one by one?
> |
> | many thanks
> |
> | tm
> |
> |
> | --
> | Timmy Mac1
> | ------------------------------------------------------------------------
> | Timmy Mac1's Profile:
> http://www.excelforum.com/member.php...o&userid=15188
> | View this thread:
> http://www.excelforum.com/showthread...hreadid=492996
> |
>
>

[Niek Otten]

This used to be the case in older versions of Excel; I can't remember which
ones.

With Excel 2003 I cannot reproduce this effect.

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:%[email protected]...
>< I've never heard of the visible state of worksheets affecting calc speed>
>
> Well, there are some effects, although I don't know about this one.
>
> If you have a large worksheet, like a 40 year financial projection with
> one row per month, and you split the window to be able to see the start
> line together with the bottom line, it takes almost
> twice as long to calculate as without the split.
> It seems as if Excel thinks it has to format all the rows for display
> instead of just one screeenful.
>
> --
> Kind regards,
>
> Niek Otten
>
> "Jim Rech" <[email protected]> wrote in message
> news:eKcT%23x%[email protected]...
>> I've never heard of the visible state of worksheets affecting calc speed.
>>
>> You need a macro to unhide worksheets all at once:
>>
>> Sub UnhideSheets()
>> Dim WS As Worksheet
>> For Each WS In Worksheets
>> WS.Visible = xlSheetVisible
>> Next
>> End Sub
>>
>>
>> --
>> Jim
>> "Timmy Mac1" <[email protected]>
>> wrote
>> in message
>> news:[email protected]...
>> |
>> | I've goneback to working on a spreadsheet recently where I had several
>> | sheets hidden. After unhiding some of these I noticed that the
>> | calculation (on automatic) was a lot slower after doing this.
>> |
>> | I'm surprised that this has such an impact. Does anyone know if it
>> | actually is a feature of excel?
>> |
>> | also, is there anyway of releasing all hidden sheets in one go or can
>> | it only be done one by one?
>> |
>> | many thanks
>> |
>> | tm
>> |
>> |
>> | --
>> | Timmy Mac1
>> | ------------------------------------------------------------------------
>> | Timmy Mac1's Profile:
>> http://www.excelforum.com/member.php...o&userid=15188
>> | View this thread:
>> http://www.excelforum.com/showthread...hreadid=492996
>> |
>>
>>
>
>

[Jim Rech]

>>This used to be the case in older versions of Excel; I can't remember
>>which
ones.

I don't doubt it, Niek. It makes sense in a way that what you've seen could
happen. It's less clear to me how worksheet tabs being visible as in the
OP's case would affect performance but anything is possible.

--
Jim
"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
| This used to be the case in older versions of Excel; I can't remember
which
| ones.
|
| With Excel 2003 I cannot reproduce this effect.
|
| --
| Kind regards,
|
| Niek Otten
|
| "Niek Otten" <[email protected]> wrote in message
| news:%[email protected]...
| >< I've never heard of the visible state of worksheets affecting calc
speed>
| >
| > Well, there are some effects, although I don't know about this one.
| >
| > If you have a large worksheet, like a 40 year financial projection with
| > one row per month, and you split the window to be able to see the start
| > line together with the bottom line, it takes almost
| > twice as long to calculate as without the split.
| > It seems as if Excel thinks it has to format all the rows for display
| > instead of just one screeenful.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| >
| > "Jim Rech" <[email protected]> wrote in message
| > news:eKcT%23x%[email protected]...
| >> I've never heard of the visible state of worksheets affecting calc
speed.
| >>
| >> You need a macro to unhide worksheets all at once:
| >>
| >> Sub UnhideSheets()
| >> Dim WS As Worksheet
| >> For Each WS In Worksheets
| >> WS.Visible = xlSheetVisible
| >> Next
| >> End Sub
| >>
| >>
| >> --
| >> Jim
| >> "Timmy Mac1" <[email protected]>
| >> wrote
| >> in message
| >> news:[email protected]...
| >> |
| >> | I've goneback to working on a spreadsheet recently where I had
several
| >> | sheets hidden. After unhiding some of these I noticed that the
| >> | calculation (on automatic) was a lot slower after doing this.
| >> |
| >> | I'm surprised that this has such an impact. Does anyone know if it
| >> | actually is a feature of excel?
| >> |
| >> | also, is there anyway of releasing all hidden sheets in one go or can
| >> | it only be done one by one?
| >> |
| >> | many thanks
| >> |
| >> | tm
| >> |
| >> |
| >> | --
| >> | Timmy Mac1
| >>
| ------------------------------------------------------------------------
| >> | Timmy Mac1's Profile:
| >> http://www.excelforum.com/member.php...o&userid=15188
| >> | View this thread:
| >> http://www.excelforum.com/showthread...hreadid=492996
| >> |
| >>
| >>
| >
| >
|
|