Deleting blanks and shifting left - cont.

[jrb]

Thanks for the efforts. I have tied them all but nothing is working.
Maybe it has to do with the enire range being selected. If there is a
charator in the column the blanks in that column does not get selected
by means of goto/special/blanks . Is there a way to go through each
cell in a range, check if it is blank if so delete it and shift left?

 

[SuperJas]

JRB

Did you have a go at my post? It will do exactly what you want in an instant

SuperJas.

 

[jrb]

SuperJas,

Thank you, yes I did try it and I couldn't get it to work either.
Maybe I am doing something wrong. When run it doesn't appear to d
anything. I select the range and then run the macro and nothing. I a
just stumped.

Jef

 

[Tom Ogilvy]

Sounds like you cells are not really blank, but just appear blank.

Dim rng as Range, cell as Range, i as Long
set rng = Selection.Columns(1).Cells

for i = rng(rng.count).row to rng(1).row step -1
set cell = cells(i,rng.column)
if len(trim(cell.Text)) = 0 then
cell.Delete Shift:=xlShiftToLeft
end if
Next