Publishing more than 1000 rows

If you try to publish a workbook with a large number of rows or columns you may get a message telling you that a sheet has too many rows or columns.

Note the small scroll bar indicating that there are a lot of rows in use.

While XLCubed Web can handle a large number of rows, browsers will hang if given very large tables to display. To avoid giving web users a poor experience XLCubed will warn you when you try to publish a sheet which is very large.

From here you have several options:

• If the sheet is used for calculation, and does not need to be shown to the user then it can be hidden. As it the sheet can not be shown in a browser the publication limit will not apply.
• If you are displaying a large Grid or Tables, consider using a Pager.
• If you really need to show such a large number of rows, the limits can be adjusted in the XLCubed options screen.
• If you don't think you are using 1000 rows you can follow the instructions below.

Reducing the used range

Excel controls the used range of the sheet, this is the limits of the cells with contents. XLCubed uses this to count the number of rows and columns used on a sheet before publication.

This normally includes cells with content, but formatting large areas can cause it to get recalculated.

Sometimes the used range can get incorrectly calculated. In this case you can delete the rows or columns you know are unused to fix the used range.

To do this:

• Select any cell in the worksheet
• Press Ctrl+End
  • This will move you to the last cell in the used range.
• Select the entire row by clicking on the row number
• Use the scrollbars to move to the end of the report
• While holding Shift, select below the last row in the report, again by clicking the row number.
• Right click and Delete the range.

You can now publish again, and the used range will be recalculated.

• If the scrollbar is still very small you can force excel to recalculate the used range by going to the VBA editor and entering the following in the immediate window.
  • ?ActiveSheet.UsedRange.Address