delete all blank cells and move all non blank cells

[jkicker]

hi all,

I have a sheet where in every row there are many blank cells and 3 cells have data in them, all in different columns. ie:

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]data1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]data3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]data5
[/TD]
[TD]data6
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]data7
[/TD]
[TD]data8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to delete all the empty cells and move all the data into the first 3 columns. ie:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]data1
[/TD]
[TD]data2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]data3
[/TD]
[TD]data4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]data5
[/TD]
[TD]data6
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]data7
[/TD]
[TD]data8
[/TD]
[/TR]
</tbody>[/TABLE]

Anybody got a macro for this?
Thanks.

 

[Rick Rothstein]

See if this macro works for you...

Sub DeleteBlanksMoveDataLeft()
  ActiveSheet.UsedRange.SpecialCells(xlBlanks).Delete xlShiftToLeft
End Sub

 

[jkicker]

Bingo! Thank you Rick!