Excel: delete blank cells, shift populated cells right

I have a very large spreadsheet with rows of data containing blank cells in the middle of a row. I need to delete the blank cells within a range and shift the populated cells to the right. I'm a vba beginner. Tried this in "this workbook": Sub Blanks() Range("A1:x9").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlRight End Sub but got an error "X 400" which I can't even find in the help screens! Can anybody be a hero here?

This question generated 20 answers. To proceed to the answers, click here.

This thread is current as of April 02, 2015.

For more resources for Microsoft Excel