As you may know Excel file stores only the rows and columns up to the last one containing some data or formatting. So if you select the last cell (row = 1,048,576 rows and column 16,384 ) and enter same value and save the file, you might come up with some huge file size.
All TM1 Perspectives developers should know that file size may have a significant impact on TM1Web performance (as the server needs to read all the data, perform calculations and rendering converting it to a web page. Files even over 200-300kb can cause performance issues.
The best practice is to delete all empty rows and the ones created under the cell with TM1RPTROW function (active form rows).
Recently I faced a situation when I was not able to delete Excel rows in a static TM1 Perspectives report. I tried everything: clearing all, deleting rows, deleting cells, using VBA activesheet.usedrange command. Nothing helped.
Finally I found a solution on the internet which worked. You need:
- Select the first empty row (after your data range)
- Scroll down to the end and holding SHIFT click the last row
- Change the height of any selected row (it will apply this height for each selected row)
- No delete the rows and save the file.
- Your issue should be fixed now
Hints: you can see the last used cell by executing VBA command: ?activesheet.usedrange.address
Cognos/TM1 