Using VBA to remove leading zeros

[Michael G. Thomas]

Hi everyone

Just a quick question about VBA within Microsoft Excel 2002. Column A in
one of my sheets has a list of supplier codes in it. The problem is that
the codes are all prefixed with multiple zeros which I'd like to remove.
I have a code snippet which can do it for a field in Microsoft Access, but
I'm not sure how to use the same code to do it to a column in Excel.

Can anybody help me out? All I wanna know is how to apply VBA code to
columns in Excel the same way that I'd apply it to a field of a table in
Access.

Thanks very much,
Michael Thomas

 

[Nick Hodge]

Michael

Depends how many cells you need to do this to. If it's only a few
tens/hundreds then the code below will do it. If it's thousands then you
could consider using a blank cell to enter a 1 in and copy that, then use
paste special>values+multiply to 'kick' them into numbers. and then delete
the spare 1. Come back if that is a better route

Sub test()
Dim myCell As Range
For Each myCell In Range("A1:A19")
myCell.Value = myCell.Value * 1
Next myCell
End Sub

(Change the range to suit...could also be made dynamic if it changes each
time)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Tom Ogilvy]

Nick's version will work if the data is all numeric (not alpha numeric). Is
that the case?

 

[Michael G. Thomas]

Yes, the data is alphanumeric unfortunately. I have the code already
though, all I need to know is how to use it. In Access, you can create a
module and then call the module using a query. Is there something similar
in Excel? Just wanna know where to put the code and how to run it.

Thanks,
Michael

 

[Tom Ogilvy]

in the VBE, do insert Module

paste you code in the module.

To run it, go back to excel and do Tools=>Macro=>Macros, select your macro
in the dialog and click run. Or while there you can click options (with
your macro selected) and set an accelerator key.

 

[Dave Peterson]

One way to get rid of leading 0's in alphanumeric data:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim iCtr As Long

With Worksheets("Sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
For Each myCell In myRng.Cells
For iCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, iCtr, 1) <> "0" Then
Exit For
End If
Next iCtr
myCell.Value = Mid(myCell.Value, iCtr)
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out--Tools|macro|macros...|Run