Custom Format Cells

[Gos-C]

Hi all,

I have the following code in a module, which I call from another code:

Please Login or Register  to view this content.

The values in column E varies in length from 1 to 10 numeric characters, e.g., 1234567, or (in a very few cases), consist of 15 alphanumeric characters – e.g., 00000000A987654.

The code is formatting column E to number format “0000000000” but the alphanumeric values still show the 15 characters.

I tried to modify it as follows:

Please Login or Register  to view this content.

But it is not working.

Can someone help me fix it, please.

Thank you,
Gos-C

[Cutter]

I'm thinking your problem is that you are trying to apply a number format to a text string.

I think you'll have to test for the alphanumeric values and remove leading zeros from the string.

[Marcol]

As Cutter says

Try this

Please Login or Register  to view this content.

This should shorten 00000000A987654 or string out A987654 to give 000A987654 without effecting any numeric entry

Avoid using select, it is usualy pointless and only slows your code execution.

Also
Why are you using xlDown? this will stop if there are any blanks in your list, assuming there is a value in E5

Is that what you need?

[Gos-C]

Hi Marcol,

It worked perfectly! Thank you very much.

I am using xlDown because there are two blank rows and thirteen Summary rows at the bottom that I do not need.

Thanks for the tip on using Select

Cutter, thanks for your suggestion.

Cheers,
Gos-C

[Marcol]

Thought there had to be a reason for xlDown.

Happy to have been of assistance.