Delete blank rows

[JakeShipley2008]

I am trying to shift about 9 columns of data that contains some data but
mostly empty rows. I would like to shift all the data in each column left.
leaving the data in the same row resulting with all data in column a. Any
help would be greatly appreciated.

 

[Roger Govier]

Hi Jake

You could insert a new column A.
In A1 enter
=LOOKUP(99^99,B1:J1)
and copy down as far as required
Then copy column A>paste Special>Values back over itself to "fix" the values
You can then delete columns B:J

 

[Code Cage Team]

It would not be possible to merge 9 columns in to 1 but all the data to
appear in column A same row as original, unless you have sporadic data e.g
column F data in Row 6 no other columns have data in row 6....etc

How is the data set up? is it like i described?

If it is as above then:
Sub blank_remove()
With ActiveSheet.UsedRange
..SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
End With
End Sub