vba

[gl45]

Anyone,
why this code isn't working:

Sub Blank()
Range("B3:HZ14").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

I'm trying to delete all the blank cells in the sheet (Range B3:HZ14), and move the data to the left, in order to have a continuous cells that contains only data, to be specific:

b=Blank cell
D=Data cell

Before delete
B C D E F (columns)...etc.
3 b b b b D D D b
4 D b D b b D D D
5 b b b b b b D b
6 D D D D D D D b
7 b b D D b b b b

After delete
B C D E F (columns)...etc.
3 D D D
4 D D D D D
5 D
6 D D D D D D D
7 D D

The workbook already contains macro that only effect the first sheet. The above macro does work with the first sheet named BOX, but I need the macro to work on a sheet named OUT.
Anyone VBA guru?
Thanks
lbj

[TheProgrammer]

Just select the sheet you want to change first (example):

Worksheets("BOX").Select
Range("B3:HZ14").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft


Worksheets("OUT").Select
Range("B3:HZ14").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft


RK

[gl45]

Randy,
Thank you
lbj

[gl45]

Randy,
as usual it never works the first time.
It does open the OUT sheet, but doesn't delete the blanks. Strange.
lbj

[TheProgrammer]

That's weird, your code worked perfectly for me.

RK

[gl45]

Randy,
I will copy/paste to a new workbook, and then run the macro. See if it works.
Thanks,
lbj

[TheProgrammer]

If it doesn't, could you send me the two sheets and I will try on my end?

Thanks...RK

[gl45]

Randy,
thanks a bunch.
lbj