Format columns using numbers instead of letters

[tiredofit]

I want to format some columns on a worksheet using numbers instead of letters.

Both versions of the following code are in a standard module.

This uses letters:

    Sheet1.Range("A:A,C:C").NumberFormat = "General"
    Sheet1.Range("B:B,D:D").NumberFormat = "0"
    Sheet1.Range("E:E").NumberFormat = "#,##0"

and this uses numbers:

    Sheet1.Range(Columns(1), Columns(3)).NumberFormat = "General"
    Sheet1.Range(Columns(2), Columns(4)).NumberFormat = "0"
    Sheet1.Range(Columns(5), Columns(5)).NumberFormat = "#,##0"

They both work IF Sheet1 is already selected.

However, if Sheet1 is NOT selected, only the one using letters work.

Is there a way to make the numbers version work without the need to select the sheet first?

Thanks

 

[Fluff]

You need to qualify all ranges

    Sheet1.Range(Sheet1.Columns(1), Sheet1.Columns(3)).NumberFormat = "General"
    Sheet1.Range(Sheet1.Columns(2), Sheet1.Columns(4)).NumberFormat = "0"
    Sheet1.Range(Sheet1.Columns(5), Sheet1.Columns(5)).NumberFormat = "#,##0"

 

[tiredofit]

You need to qualify all ranges

    Sheet1.Range(Sheet1.Columns(1), Sheet1.Columns(3)).NumberFormat = "General"
    Sheet1.Range(Sheet1.Columns(2), Sheet1.Columns(4)).NumberFormat = "0"
    Sheet1.Range(Sheet1.Columns(5), Sheet1.Columns(5)).NumberFormat = "#,##0"

Of course, I forgot that.

Thanks for the speedy response.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

I think that you need to check those results carefully.

For example Sheet1.Range(Sheet1.Columns(1), Sheet1.Columns(3)).NumberFormat = "General" will format columns A, B and C as general, not just columns A and C as per your initial code.
Of course similar for the second line formatting B, C and D
Finally, not an error, but there is no need to specify column 5 (E) twice.

I think this will actually do what your original code did. It also uses the typing and code efficiency of only having to specify the sheet once.

With Sheet1
  Union(.Columns(1), .Columns(3)).NumberFormat = "General"
  Union(.Columns(2), .Columns(4)).NumberFormat = "0"
  .Columns(5).NumberFormat = "#,##0"
End With

 

[tiredofit]

I think that you need to check those results carefully.

For example Sheet1.Range(Sheet1.Columns(1), Sheet1.Columns(3)).NumberFormat = "General" will format columns A, B and C as general, not just columns A and C as per your initial code.
Of course similar for the second line formatting B, C and D
Finally, not an error, but there is no need to specify column 5 (E) twice.

I think this will actually do what your original code did. It also uses the typing and code efficiency of only having to specify the sheet once.

With Sheet1
  Union(.Columns(1), .Columns(3)).NumberFormat = "General"
  Union(.Columns(2), .Columns(4)).NumberFormat = "0"
  .Columns(5).NumberFormat = "#,##0"
End With

Thanks.

I realised afterwards there were differences between formatting column A, B and C, as opposed to just A and C.

The purpose of all this was to use enums to refer to columns instead of letters, so if extra columns were added or removed, the code could be changed easily, or so I thought!

After all this, I think it's actually clearer to write:

    Sheet1.Range("A:A,C:C").NumberFormat = "General"
    Sheet1.Range("B:B,D:D").NumberFormat = "0"
    Sheet1.Range("E:E").NumberFormat = "#,##0"

than:

    Sheet1.Range(Sheet1.Columns(DataCols.Name), Sheet1.Columns(DataCols.Address)).NumberFormat = "General"
    Sheet1.Range(Sheet1.Columns(DataCol.Age), Sheet1.Columns(DataCols.NumbersBought)).NumberFormat = "0"
    Sheet1.Range(Sheet1.Columns(5), Sheet1.Columns(DataCols.Salary)).NumberFormat = "#,##0"

 

[Peter_SSs]

That does not look like a comparison between using letters and numbers per your original question.

That second code looks quite unusual. It would be good to see the whole code so we knew what those new elements are and also to know what is on Sheet1 and where. I suspect there would be a better way.

 

[tiredofit]

That does not look like a comparison between using letters and numbers per your original question.

That second code looks quite unusual. It would be good to see the whole code so we knew what those new elements are and also to know what is on Sheet1 and where. I suspect there would be a better way.

This is the full code:

Option Explicit

Public Enum DataCols

    Name = 1
    Age
    Address
    NumbersBought
    Salary
    
End Enum

Sub UsingLetters()

    Sheet1.Range("A:A,C:C").NumberFormat = "General"
    Sheet1.Range("B:B,D:D").NumberFormat = "0"
    Sheet1.Range("E:E").NumberFormat = "#,##0"
    
End Sub

Sub UsingNumbers()

    Union(Sheet1.Columns(DataCols.Name), Sheet1.Columns(DataCols.Address)).NumberFormat = "General"
    Union(Sheet1.Columns(DataCols.Age), Sheet1.Columns(DataCols.NumbersBought)).NumberFormat = "0"
    Sheet1.Range(Sheet1.Columns(DataCols.Salary), Sheet1.Columns(DataCols.Salary)).NumberFormat = "#,##0"
    
End Sub

 

[Peter_SSs]

Thanks for the additional code.
Perhaps I'm missing the point but I don't see even a potential advantage in using a system like that. Instead of swapping letters for numbers, in writing the code you are actually swapping letters for words. DataCols.Name is more cumbersome than just using the number 1 directly.

The purpose of all this was to use enums to refer to columns instead of letters, so if extra columns were added or removed, the code could be changed easily, or so I thought!

What about something like this where you just have to change the const line if columns are added or removed?

Sub UsingNumbers_Alt1()
  Const NameCol As String = "A"
 
  With Sheet1.Columns(NameCol)
    Union(.Offset(, 0), .Offset(, 2)).NumberFormat = "General"
    Union(.Offset(, 1), .Offset(, 3)).NumberFormat = "0"
    .Offset(, 4).NumberFormat = "#,##0"
  End With
End Sub

.. or, guessing from your Enum setup that the first column that you want to format has a heading of "Name", perhaps this which would not need any adjustment if columns were added/removed?

Sub UsingNumbers_Alt2()
  Dim nc As Long 'Name column
 
  With Sheet1
    nc = .Rows(1).Find(What:="Name", LookAt:=xlWhole, MatchCase:=False).Column
    Union(.Columns(nc), .Columns(nc + 2)).NumberFormat = "General"
    Union(.Columns(nc + 1), .Columns(nc + 3)).NumberFormat = "0"
    .Columns(nc + 4).NumberFormat = "#,##0"
  End With
End Sub

 

[tiredofit]

Thanks for the additional code.
Perhaps I'm missing the point but I don't see even a potential advantage in using a system like that. Instead of swapping letters for numbers, in writing the code you are actually swapping letters for words. DataCols.Name is more cumbersome than just using the number 1 directly.


What about something like this where you just have to change the const line if columns are added or removed?

Sub UsingNumbers_Alt1()
  Const NameCol As String = "A"
 
  With Sheet1.Columns(NameCol)
    Union(.Offset(, 0), .Offset(, 2)).NumberFormat = "General"
    Union(.Offset(, 1), .Offset(, 3)).NumberFormat = "0"
    .Offset(, 4).NumberFormat = "#,##0"
  End With
End Sub

.. or, guessing from your Enum setup that the first column that you want to format has a heading of "Name", perhaps this which would not need any adjustment if columns were added/removed?

Sub UsingNumbers_Alt2()
  Dim nc As Long 'Name column
 
  With Sheet1
    nc = .Rows(1).Find(What:="Name", LookAt:=xlWhole, MatchCase:=False).Column
    Union(.Columns(nc), .Columns(nc + 2)).NumberFormat = "General"
    Union(.Columns(nc + 1), .Columns(nc + 3)).NumberFormat = "0"
    .Columns(nc + 4).NumberFormat = "#,##0"
  End With
End Sub

Thanks, that's a good idea.