CODEWITHSUNDEEP
excelvba
user2119980
user2119980

Reputation: 509

Macro to run on the column I have selected

I have the following macro, it adds a number of zeroes to the beginning of a string of numbers until the number has a total of 7 digits. Currently it only does the A column, I would like for it to run the macro for whichever column I have selected so I do not always have to cut and paste and recut and paste all the columns I need to run it on. ANy ideas?

Sub AddZeroes1()
'Declarations
Dim cl As Range
Dim i As Long, endrow As Long

Application.ScreenUpdating = False
    'Converts the A column format to Text format
    Columns("A:A").NumberFormat = "@"
    'finds the bottom most row
    endrow = ActiveSheet.Range("A1048576").End(xlUp).Row
    '## Or, for Excel 2003 and prior: ##'
    'endrow = ActiveSheet.Range("A65536").End(xlUp).Row

    'loop to move from cell to cell
    For i = 1 To endrow - 1
        Set cl = Range("A" & i)
        With cl
        'The Do-While loop keeps adding zeroes to the front of the cell value until it         hits     a length of 7
            Do While Len(.Value) < 7
                .Value = "0" & .Value
            Loop
        End With
    Next i
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 10765

Answers (3)

Jon Crowell
Jon Crowell

Reputation: 22330

You can update as many columns as you want by changing the target to the selection rather than a specific column. (as suggested by t.thielemans)

Try this:

Sub AddZeroesToSelection()
     Dim rng As Range
     Dim cell As Range

     Set rng = Selection
     rng.NumberFormat = "@"

     For Each cell In rng
        Do While Len(cell.Value) < 7
          cell.Value = "0" & cell.Value
        Loop
     Next cell

End Sub

Upvotes: 2

tigeravatar
tigeravatar

Reputation: 26640

From your question:

it adds a number of zeroes to the beginning of a string of numbers until the number has a total of 7 digits

If you simply want numbers to show leading 0's until the numbers are 7 digits long, you can use a custom format of: 0000000

For example:

    123
   5432
     26
9876543

Select the cells -> right-click -> Format Cells -> Custom -> Type in "0000000" (no quotes) -> OK

Now they should appear with the leading 0's:

0000123
0005432
0000026
9876543

If it has to be a macro, then this should work:

Sub AddZeroes1()

    Selection.NumberFormat = "0000000"

End Sub

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Change only the MyCol line:

Sub AddZeroes1()
Dim cl As Range
Dim i As Long, endrow As Long
Dim MyCol As String
MyCol = "A"
Application.ScreenUpdating = False
    Columns(MyCol & ":" & MyCol).NumberFormat = "@"
    endrow = ActiveSheet.Range(MyCol & "1048576").End(xlUp).Row
    For i = 1 To endrow - 1
        Set cl = Range(MyCol & i)
        With cl
            Do While Len(.Value) < 7
                .Value = "0" & .Value
            Loop
        End With
    Next i
Application.ScreenUpdating = True
End Sub

NOT TESTED

Upvotes: 1

Related Questions