CODEWITHSUNDEEP
excelexcel-formulavba
Aurelius
Aurelius

Reputation: 485

Move data to the leftmost blank cell with VBA

In essence, I need an automatic way to move all cells the very left most blank cell.

I've made an example: enter image description here

Example of unsorted data in the blue, how I want it to look when sorted in red.

Like I say, the way it needs to be sorted is, in theory, by moving it left through blank cells until the cell on the left is not blank. I presume this would be a VBA solution, but I am unsure where to begin.

Upvotes: 0

Views: 1903

Answers (2)

Slai
Slai

Reputation: 22876

The Excel version:
Home > Find & Select > Go To Special > Blanks > OK.
Then Delete > Delete Cells ( or Ctrl + - ) > Shift cells left > OK

In VBA the above can be shortened to

Cells.SpecialCells(xlCellTypeBlanks).Delete xlToLeft

Upvotes: 1

SJR
SJR

Reputation: 23081

This will do that, but it won't necessarily match up the entries (in your example all the cars and trees end up in the same column). It uses the very handy SpecialCells method to find all the blank cells in a range and deletes them, in this case shifting the adjacent cell to the left.

Sub x()

On Error Resume Next
With Range("A1").CurrentRegion
    .Offset(, 1).Resize(, .Columns.Count - 1).SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
End With

End Sub

Upvotes: 3

Related Questions