Leading zeros

[Excelnewbie001]

I have the following code to add a leading zero to my data in column E1-E20 the problem is when I run this macro which is set to 3 digits it runs down 210 lines adding a 0 -it musnt only the data in column E -in this example the 4 should be 004 and the 40 should be a 400 it must leave all the data thats already in 3 numbers so it should only adress the leading zero -this code fix the leading zero but it doesnt fix the 40 adding a zero at the end -any help much appreciated as it needs tweaking
[TABLE="width: 115"]
<tbody>[TR]
[TD]Sample data in

Column E
4[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]40[/TD]
[/TR]
[TR]
[TD]13[/TD]
[/TR]
[TR]
[TD]301[/TD]
[/TR]
[TR]
[TD]310[/TD]
[/TR]
[TR]
[TD]211[/TD]
[/TR]
[TR]
[TD]220[/TD]
[/TR]
[TR]
[TD]130[/TD]
[/TR]
[TR]
[TD]121

This is the macro code

Sub LeadZ()
Dim Rng As Range
Dim rVal As String
Dim selCol As Range
Sheets("Test").Select
Range("E:E").Select


Set selCol = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))
    
  For Each Rng In selCol
    Rng.Select
    Selection.NumberFormat = "@"
    rVal = Rng.Value
    If Len(Rng.Value) < 3 Then
    Rng.Value = "0" & rVal
    End If
    
   Next
 
MsgBox "Process is complete."
End Sub

[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row

Hi Rick, I deliberately chose the Find method over the Rows.Count as I felt it safer as not having enough info on the usedrange issue.

 

[Excelnewbie001]

Here is another way to write your LeadZ macro...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub LeadZ()
  Dim Addr As String
  Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr).NumberFormat = "@"
  Range(Addr) = Evaluate(Replace("IF(LEN(@)<3,""00""&@,@)", "@", Addr))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Fantastic thank you Rick this works too !

 

[Excelnewbie001]

Hi Rick, I deliberately chose the Find method over the Rows.Count as I felt it safer as not having enough info on the usedrange issue.

That works perfectly thank you Mark