MrExcel Publishing
Your One Stop for Excel Tips & Solutions

New to Excel


Posted by richard on February 28, 2001 2:27 PM

How would I select the entire column 'A' using VB Code, and say any thing in
this column that is less than 5 characters add a '0' to the front of the
characters.

Thank you in advance

Regards,
Richard


Posted by David Hawley on February 28, 2001 3:36 PM


Hi Richard

I would avoid apply this to the entire Column as it would be a bit slow. Try this method instead.

Sub AddZero()
Dim LookRange As Range, Cell As Range
Set LookRange = Columns(1).SpecialCells(xlCellTypeConstants)
Application.ScreenUpdating = False
For Each Cell In LookRange
If Len(Cell) < 5 Then
Cell = "'0" & Cell
End If
Next
Application.ScreenUpdating = True
End Sub

....Or if you have no blanks (or can afford to have them removed) I have another method using Excels Autofilter in VBA that would be as quick as you will get.


Dave


OzGrid Business Applications