Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "E").Value < "" Then

If Len(.Cells(i, "E").Text) < 6 Then

.Cells(i, "E").Value = "'" & Left("00000", 6 - _
Len(.Cells(i, "E").Value)) & _
.Cells(i, "E").Value
End If
End If
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Not sure why you posted your message (I don't see it as a response to
anything in my newsreader); however I wanted to point out that this line....

.Cells(i, "E").Value = "'" & Left("00000", 6 - _
Len(.Cells(i, "E").Value)) & _
.Cells(i, "E").Value

can be replaced with this slightly shorter (and perhaps more
straightforward) one...

..Cells(i, "E").Value = "'" & Right("000000" & .Cells(i, "E").Value, 6)

Rick


"Bob Phillips" wrote in message
...
Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "E").Value < "" Then

If Len(.Cells(i, "E").Text) < 6 Then

.Cells(i, "E").Value = "'" & Left("00000", 6 - _
Len(.Cells(i, "E").Value)) & _
.Cells(i, "E").Value
End If
End If
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)