CODEWITHSUNDEEP
vbaexcel
sudonym
sudonym

Reputation: 4028

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

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)?

Upvotes: 1

Views: 637

Answers (2)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

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")

Upvotes: 2

user4039065
user4039065

Reputation:

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

Upvotes: 3

Related Questions