Search notes:

Office Object Model: Excel.Worksheet.usedRange

Worksheet's usedRange property returns the range that have had or still have a value.

This range can be reset with the range's clearContents and clearFormats functions.

option explicit

sub main() ' {

    dim sh as workSheet
    set sh = activeSheet

    debug.print sh.usedRange.address(referenceStyle := xlR1C1) ' R1C1

    cells(7, 5) = "foo"
    debug.print sh.usedRange.address(referenceStyle := xlR1C1) ' R7C5

    cells(3, 8) = "bar"
    debug.print sh.usedRange.address(referenceStyle := xlR1C1) ' R3C5:R7C8

    sh.usedRange.clearContents
    debug.print sh.usedRange.address(referenceStyle := xlR1C1) ' R1C1

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Worksheet/usedRange.bas

Auto fitting the usedRange's columns

After programmmatically filling an Excel Worksheet with data, the column widths can be set to the width that is required to show all the data with something like

activeSheet.usedRange.columns.autofit

Office Object Model: Excel.Worksheet.usedRange

Auto fitting the usedRange's columns

See also