How to shift an excel UsedRange to begin at cell A1 its worksheet (i.e. delete all blank rows and columns outside UsedRange)?

1
votes

I am new to VBA and have not yet been able to figure out how to select and delete the "non-UsedRange" from a sheet at once (i.e. shift the UsedRange to begin at cell A1).

In other words, I need a dynamic solution to move a UsedRange of variable size from its variable location to the very top left of its worksheet (i.e. all empty columns/rows on the left/top side of the UsededRange should disapear).

I have (probably) found a one-liner to do this (only) row-wise for (only) one specific column:

ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

How to shift an excel UsedRange to begin at cell A1 its worksheet (i.e. delete all blank rows and columns outside UsedRange)?

2
vbaexcel

2 Answers

2
votes

What about Cut >> Paste:

ActiveSheet.UsedRange.Cut
ActiveSheet.Paste Range("A1")

or, as @JohnyL suggested in comment, use short version:

ActiveSheet.UsedRange.Cut Range("A1")
3
votes

Just keep deleting row 1 and column A while they are blank.

with worksheets("sheet1")
    do while not cbool(application.counta(.rows(1).cells))
        .rows(1).entirerow.delete
    loop
    do while not cbool(application.counta(.columns(1).cells))
        .columns(1).entirecolumn.delete
    loop
end with