locked
EXCEL: How to reduce the "Worksheet.UsedRange" Size RRS feed

  • Question

  • Hello community,

    when having filled accidentally one complete column and/or row in EXCEL, the "Worksheet.UsedRange" property is set to the maximum number of rows and/or columns. Clearing that column/Row to "Empty" and deleting those rows/columns manually still leaves UsedRange property to the maximal values.

    Has anybody a hint, how to shrink those numbers to the actual outer boundary of used cells? I need this, because I "SaveAs" Worksheets to CSV-files. They will otherwise contain all empty rows and columns nicely coded in delimiter-only lines.


    Regards Uwe



    Sunday, January 18, 2015 1:45 PM

Answers

  • Re:  resetting UsedRange

    Select the "blank" rows or columns by selecting the headers not the cells.
    Clear by choosing "Delete" from the right-click popup menu, not by pressing the delete key.
    Save the workbook.
    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)




    Sunday, January 18, 2015 6:20 PM
  • Hi Jim,

    thanks a lot for your detailed hint. It does work when I tested with full-length columns with constants in all cells. The scrollbar immediately shows the change by lengthening the handle.

    Unfortunately this does not work for my rather complex sheets, I'm afraid. Doing exactly what you have specified clears all the rows but the full set of empty rows is shown and fill up the worksheet up to the maximum number of rows. After closing and reopening the workbook, all empty rows are still there. Doing this with a VBA program has the same result. The Worksheet.UsedRange still shows the full number of rows, too. And the workbook size never shrinks below 7 MB . I think it is an bug. Unfortunately Connect does not accept bug reports for Office.

    My workaround is a little VBA where I create a new empty worksheet, then cut/past the selection with Cut-method from the broken worksheet to the new with data, formulas and all formatting, then setting the row heights of all rows and finally renaming the new sheet and deleting the old. Works quite well. Even Names have survived the renaming of the sheets and are still there.


    Regards Uwe



    • Marked as answer by Jörg Debus Saturday, January 24, 2015 9:16 AM
    • Edited by Jörg Debus Saturday, January 24, 2015 9:28 AM
    Saturday, January 24, 2015 9:16 AM

All replies

  • Re:  resetting UsedRange

    Select the "blank" rows or columns by selecting the headers not the cells.
    Clear by choosing "Delete" from the right-click popup menu, not by pressing the delete key.
    Save the workbook.
    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)




    Sunday, January 18, 2015 6:20 PM
  • Hi Jim,

    thanks a lot for your detailed hint. It does work when I tested with full-length columns with constants in all cells. The scrollbar immediately shows the change by lengthening the handle.

    Unfortunately this does not work for my rather complex sheets, I'm afraid. Doing exactly what you have specified clears all the rows but the full set of empty rows is shown and fill up the worksheet up to the maximum number of rows. After closing and reopening the workbook, all empty rows are still there. Doing this with a VBA program has the same result. The Worksheet.UsedRange still shows the full number of rows, too. And the workbook size never shrinks below 7 MB . I think it is an bug. Unfortunately Connect does not accept bug reports for Office.

    My workaround is a little VBA where I create a new empty worksheet, then cut/past the selection with Cut-method from the broken worksheet to the new with data, formulas and all formatting, then setting the row heights of all rows and finally renaming the new sheet and deleting the old. Works quite well. Even Names have survived the renaming of the sheets and are still there.


    Regards Uwe



    • Marked as answer by Jörg Debus Saturday, January 24, 2015 9:16 AM
    • Edited by Jörg Debus Saturday, January 24, 2015 9:28 AM
    Saturday, January 24, 2015 9:16 AM