Custom Format Cells

[Gos-C]

Hi all,

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

Option Explicit
Sub Format_Columns_2()
    Range("D5", Range("D5").End(xlDown)).Select
    Selection.NumberFormat = "000000"
    Range("E5", Range("E5").End(xlDown)).Select
    Selection.NumberFormat = "0000000000"
    Range("G5", Range("G5").End(xlDown)).Select
    Selection.NumberFormat = "00000000"
    Range("I5", Range("I5").End(xlDown)).Select
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(@_)"
    Range("J5", Range("J5").End(xlDown)).Select
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(@_)"
    Range("L5", Range("L5").End(xlDown)).Select
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(@_)"
    Range("A:G,K:K,M:M").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
End Sub

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:

Option Explicit
Sub Format_Columns_2()
Dim LastRow As Integer, vRow As Integer
LastRow = Range("E" & Rows.Count).End(xlUp).Row
For vRow = 5 To LastRow - 16
   If Len(Range("E" & vRow)) > 10 Then
    Range("E" & vRow).Value = Right("E" & vRow, 10)
   End If
  Next
    Range("D5", Range("D5").End(xlDown)).Select
    Selection.NumberFormat = "000000"
    Range("E5", Range("E5").End(xlDown)).Select
    Selection.NumberFormat = "0000000000"
    Range("G5", Range("G5").End(xlDown)).Select
    Selection.NumberFormat = "00000000"
    Range("I5", Range("I5").End(xlDown)).Select
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(@_)"
    Range("J5", Range("J5").End(xlDown)).Select
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(@_)"
    Range("L5", Range("L5").End(xlDown)).Select
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(@_)"
    Range("A:G,K:K,M:M").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
End Sub

But it is not working.

Can someone help me fix it, please.

Thank you,
Gos-C