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

Thank you in advance


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
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.


OzGrid Business Applications