Thread
:
Using VBA to remove leading zeros
View Single Post
#
2
Posted to microsoft.public.excel.programming
Nick Hodge
external usenet poster
Posts: 1,173
Using VBA to remove leading zeros
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 specialvalues+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
HIS
"Michael G. Thomas" wrote in message
news:opsd7likm6oizlpb@michael...
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
Reply With Quote
Nick Hodge
View Public Profile
Find all posts by Nick Hodge