I have a table in which each cell contains either a number or is blank.
On each row, I would like to delete all the blank cells to the left of
the first number and shift cells left.

Thanks.

Off the top

For Each cell in Selection
cell.Value = Trim(cell.Value)
cell.Numberformat = "@"
Next cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JG Scott" wrote in message
oups.com...
I have a table in which each cell contains either a number or is blank.
On each row, I would like to delete all the blank cells to the left of
the first number and shift cells left.

Thanks.

The following example assumes that the 2 columns of data are Columns A and B
and it starts running with the cellpointer in first value in column B
================================================== ====
Sub MoveToBlankCell()

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim iRowCounter As Integer

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")

Range("B1").Select

varVal = ActiveCell.Value

Do Until varVal = ""
If ActiveCell.Offset(iRowCounter, -1).Value = "" Then
ActiveCell.Offset(iRowCounter).Cut
ActiveCell.Offset(iRowCounter, -1).Delete Shift:=xlShiftToLeft
End If
iRowCounter = iRowCounter + 1
varVal = ActiveCell.Offset(iRowCounter).Value
Loop

Set wb = Nothing
Set ws = Nothing

End Sub
================================================== ====
--
Kevin Backmann


"JG Scott" wrote:

I have a table in which each cell contains either a number or is blank.
On each row, I would like to delete all the blank cells to the left of
the first number and shift cells left.

Thanks.

Bob,

Thanks, but I think you misunderstand. I am not trying to delete
blanks within cells, but rather to delete blank cells entirely and
shift cells to the left.

JG Scott