Delete Empty Cells in excel 2007 and Shift Left

[rbrown8432]

How would I delete empty cells and shift remaining data to the left in excel 2007 for multiple fields? Example: If data range starts in C1 and ends in T2686

 

[VBAdude]

Try this..

Sub DeleteEmptyCells()

Dim MyRange As Range
Dim FinalRow As Long

FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
Set MyRange = Range("C1:T" & FinalRow)

MyRange.SpecialCells(xlBlanks).Delete shift:=xlToLeft

End Sub

 

[rbrown8432]

I opened excel, hit Alt F11, chose, This Workbook, and pasted the code into the script editor. Chose tools, macro, then run and nothing happened.

Thanks

 

[VBAdude]

hmmm works on my end...

In the project pane, right click the name of your book, and click insert module, and paste the code there. Also, keep in mind, the macro's range is column C through T as you indicated above...you would have to change MyRange in the code to reference different ranges.

 

[rbrown8432]

Than you. I saved the file as a csv formate and opened it in a notepad. It revealed that there were commas. That is the reason it would not identify the empty cell as EMPTY. Seems a little elementary however I used the find and replace all feature and chose the multiple commas in exchange for a comma. it replaced them and shifted all the data.