Deleting empty cells in Excel

[traceyr]

I have a large spreadsheet (20 cols x 16,000 rows) which represents a hierarchical cost centre structure; the parent cost centre is in col 1 and the children across the page. Where a level is not present there is an empty cell.

I would like to process each row, deleting empty cells and shifting all remaining cells to the left.
When it is finished I need to export the result as a csv, if possible without trailing commas.

Is this possible with an Excel macro?
Any ideas anyone?
Thanks in advance for your help.
Tracey

 

[luceze]

To get rid of the blanks you could use this macro to get rid of the blanks.

Sub DeleteBlanks()
Dim cell
For Each cell In Selection
cell.Select
If ActiveCell = "" Then
Selection.Delete Shift:=xlToLeft
End If
Next
End Sub

Just highlight the range of data you wish to perform the action on and run the macro.


HTH,
Eric

 

[KenWright]

Or even just select the area concerned, do Edit / Go To / Special / Blank Cells Only, then do Edit / Delete / Shift Cells left.

Regards
Ken................

 

[traceyr]

Eric,

Thanks. It does the job well (but slowly! :-( )

Tracey