Tweak Code for Cell Padding

[bradjedis]

Greetings,

I have this code below. I am in need of a couple of things for this to accomplish.

1) Ignore empty cells
2) process two distinct columns (H and M) and only process to the last cell that contains data in each col.
) Process across all Worksheets in the workbook.

Code: Select all

Sub PadWithFiveZeros()
    Dim rng As Range
    Dim cl As Range
    For Each cl In Range("h2", Range("h1048576").End(xlUp))
        cl.Value = Right("00000" & cl.Value, 5)
    Next cl
End Sub

[HansV]

Like this:

Code: Select all

Sub PadWithFiveZeros()
    Dim v As Variant
    Dim cl As Range
    Application.ScreenUpdating = False
    For Each v In Array("H", "M")
        For Each cl In Range(Cells(2, v), Cells(Rows.Count, v).End(xlUp))
            If cl.Value <> "" Then
                cl.Value = Right("00000" & cl.Value, 5)
            End If
        Next cl
    Next v
    Application.ScreenUpdating = True
End Sub

[bradjedis]

Hans,

will this cycle thru each worksheet unassisted?

[HansV]

Sorry, I missed that (did you add it later?)

Code: Select all

Sub PadWithFiveZeros()
    Dim w As Worksheet
    Dim v As Variant
    Dim cl As Range
    Application.ScreenUpdating = False
    For Each w In Worksheets
        For Each v In Array("H", "M")
            For Each cl In w.Range(w.Cells(2, v), w.Cells(w.Rows.Count, v).End(xlUp))
                If cl.Value <> "" Then
                    cl.Value = Right("00000" & cl.Value, 5)
                End If
            Next cl
        Next v
    Next w
    Application.ScreenUpdating = True
End Sub

[bradjedis]

Actually, no. I missed putting a "3)" to draw attention...


I will give this a go an let you know soon.


Thanks,

[bradjedis]

Works Great!