vba
 

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

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

Randy,
Thank you
lbj

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

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

RK

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

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

Thanks...RK

Randy,
thanks a bunch.
lbj