Deleting empty rows – reset the last used cell in Excel

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:

  1. Select the first empty row (after your data range)
  2. Scroll down to the end and holding SHIFT click the last row
  3. Change the height of any selected row (it will apply this height for each selected row)
  4. No delete the rows and save the file.
  5. Your issue should be fixed now

 

Hints: you can see the last used cell by executing VBA command: ?activesheet.usedrange.address

Recent Posts

Recent Comments

Meta

Written by:

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *