Microsoft KB Archive/276249

= PRB: Long Delay When You Navigate Away from a Web Page That Contains Office Spreadsheet Component =

Article ID: 276249

Article Last Modified on 11/12/2001

-

APPLIES TO


 * Microsoft Office Spreadsheet Component 9.0 1.0

-



This article was previously published under Q276249



SYMPTOMS
When you navigate away from a Web page that contains a Spreadsheet component, you experience a long delay before the next page is displayed in the browser. During this delay, note that the cube at the upper-left corner of the Spreadsheet component spins to indicate that the Spreadsheet component is still working.



CAUSE
The long delay may occur if there is a large amount of information that the Spreadsheet component must persist prior to when it unloads the Web page. The amount of persisted data might be larger than you expect if the last &quot;dirty&quot; cell on the Spreadsheet is much farther down or across the Spreadsheet than the actual range that contains the data. Unused cells on the Spreadsheet may be dirtied unexpectedly if you apply a format or perform some other action to entire rows or columns on the Spreadsheet.

The &quot;More Information&quot; section describes scenarios where you might experience this behavior, and also provides tips to resolve a long delay while you navigate away from the Web page.



STATUS
This behavior is by design.



Scenario 1: Manipulate Entire Rows and Columns
The following HTML generates a Web page that contains a Spreadsheet component. Data is added to cells A1:F300 and the unused columns of the Spreadsheet are hidden. You might expect the component to report the UsedRange property for the sheet to be A1:F300 (300 rows x 6 columns = 1,800 cells). However, hiding columns G:ZZ dirties rows 1 through 300, so the component actually reports the UsedRange property as 1:300 (300 rows x 702 columns = 210,600 cells). Therefore, when you navigate away from this Web page, the component persists information for all 210,600 cells, rather than just 1,800 cells as you expect.      Sub Window_onload SSheet.ActiveSheet.Range(&quot;A1:F300&quot;).Value = &quot;test&quot; SSheet.ActiveSheet.Range(&quot;G1:ZZ1&quot;).EntireColumn.Hidden = True MsgBox SSheet.ActiveSheet.UsedRange.Address End Sub    The delay you might experience in this scenario can be corrected easily if you set the ViewableRange property for the Spreadsheet component to a specific range rather than hide entire rows or columns on the Spreadsheet: Sub Window_onload SSheet.ActiveSheet.Range(&quot;A1:F300&quot;).Value = &quot;test&quot; SSheet.ViewableRange = SSheet.ActiveSheet.UsedRange.Address MsgBox SSheet.ActiveSheet.UsedRange.Address End Sub

Scenario 2: Apply AutoFilters to Entire Columns
Another scenario is when you apply an AutoFilter to entire columns on the Spreadsheet:    </OBJECT> <SCRIPT LANGUAGE=vbscript> Sub Window_onload SSheet.ActiveSheet.Range(&quot;A1:C1&quot;).Value = Array(&quot;Field1&quot;, &quot;Field2&quot;, &quot;Field3&quot;) SSheet.ActiveSheet.Range(&quot;A2:C300&quot;).Formula = &quot;=MOD(ROW,4)&quot; SSheet.ActiveSheet.Range(&quot;A1:C1&quot;).EntireColumn.AutoFilter End Sub Sub Window_onBeforeUnload MsgBox SSheet.ActiveSheet.UsedRange.Address End Sub </SCRIPT> </BODY> </HTML> After this HTML page is loaded, if you apply a filter to exclude some of the rows in the Spreadsheet and then navigate away from the page, the BeforeUnload event may report the UsedRange of the Spreadsheet to be A:C (65,536 rows x 3 columns = 196,608 cells). As in the previous scenario, apply the AutoFilter to a specific range instead of entire columns to resolve the long delay in navigation: SSheet.ActiveSheet.UsedRange.AutoFilter

Scenario 3: Spreadsheet Built at Design Time
Spreadsheets can be set up in design time with tools such as Microsoft FrontPage or Microsoft Visual InterDev. If you apply formats or add data to a Spreadsheet component when you design your Web page, you might also find that the last cell on the Spreadsheet is much farther down or farther across the Spreadsheet than you expect. When you design your Web page, press the CTRL+END keys to determine the address of the last cell on the Spreadsheet.

To correct unnecessary delays when you navigate away from your Web page, reset the last cell by following these steps:
 * 1) Select the column header for the first unused column on the Spreadsheet.
 * 2) Press CTRL+SHIFT+RIGHT ARROW, and note that this selects all of the columns from the column that you selected through column ZZ.
 * 3) Right-click the selection, choose Delete from the menu, and then choose Columns.
 * 4) Select the row header for the first unused row on the Spreadsheet.
 * 5) Press CTRL+SHIFT+DOWN ARROW, and note that this selects all rows from the row you selected through row 65,536.
 * 6) Right-click the selection, select Delete from the menu, and then choose Rows.
 * 7) Save your changes.

Additional query words: webcalc used range usedrange last cell

Keywords: kbprogramming kbprb kbofficewebspread KB276249

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.