Hello,
I have a column of numbers with a few cells being blank. I am using the
following code to delete the blank cells: and shift the numbers up.

LR = cells(1,1).end(xldown).row
for i = 1 to lr
if isempty(cells(i,1))=true then
cells(i,1).delete shift:=xlup
i = i -1
end if
next i

I get in a loop because of the i = 1 -1. I need that to ensure there are
not two blank cells together.

Any ideas.

Thanks

Bill

Hi Bill,

With your existing method, you should start at the bottom and work upwards.

As another approach, try:

On Error Resume Next
Columns("A").SpecialCells(xlBlanks).Delete shift:=xlUp
On Error GoTo 0


---
Regards,
Norman



"Bill" wrote in message
ink.net...
Hello,
I have a column of numbers with a few cells being blank. I am using the
following code to delete the blank cells: and shift the numbers up.

LR = cells(1,1).end(xldown).row
for i = 1 to lr
if isempty(cells(i,1))=true then
cells(i,1).delete shift:=xlup
i = i -1
end if
next i

I get in a loop because of the i = 1 -1. I need that to ensure there are
not two blank cells together.

Any ideas.

Thanks

Bill

It is a little easier than all of that... Try this

Range("A:A").SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
--
HTH...

Jim Thomlinson


"Bill" wrote:

Hello,
I have a column of numbers with a few cells being blank. I am using the
following code to delete the blank cells: and shift the numbers up.

LR = cells(1,1).end(xldown).row
for i = 1 to lr
if isempty(cells(i,1))=true then
cells(i,1).delete shift:=xlup
i = i -1
end if
next i

I get in a loop because of the i = 1 -1. I need that to ensure there are
not two blank cells together.

Any ideas.

Thanks

Bill