Left Justify Rows of Numbers
 

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.

Left Justify Rows of Numbers
 

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.

Left Justify Rows of Numbers
 

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.

Left Justify Rows of Numbers
 

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