Try this. I haven't tested it, but it works in theory and it should b
quite a bit faster:

Sub NumberMassage()
Application.ScreenUpdating = False
Range("A:E").Sort Key1:=Range("A1"), Order1:=xlDescending
While Cells(1, 1).Value 9999999
badNum = Cells(1, 1).Value
newNum = Left(badNum, 7)
badNum = Trim(Right(badNum, Len(badNum) - 7))
ActiveCell.Value = badNum
Rows(1).Copy
Rows(1).Insert
Cells(1, 1).Value = newNum
Wend
Range("A:E").Sort Key1:=Range("A1"), Order1:=xlDescending
Application.ScreenUpdating = True
End Sub

I should add that the reason it should be faster is that selectin
cells takes up alot of time when you do it as many times as you will b
here and updating the screen with every one of those changes takes LOT
of time. By not updating the screen and avoiding unnecessar
selections you can increase your speed immensely. - piku

--
Message posted from http://www.ExcelForum.com