Formatting in a macro

Please post your code.

Regards, TMS

@Philb1: in your code, you define and set the Ws variable, but then you refer to the Activesheet and qualify all the ranges with Ws.

Somewhat belatedly, a few variations on a theme, starting with a simple tidy up of the original code.

Option Explicit

Sub Test2()
' works with the active worksheet
' simple approach; no select ... selection

Range(Range("A1"), Range("A1").End(xlDown)).NumberFormat = "0"
Range(Range("B1"), Range("B1").End(xlDown)).NumberFormat = "0"
Range(Range("D1"), Range("D1").End(xlDown)).NumberFormat = "0"
Range(Range("F1"), Range("F1").End(xlDown)).NumberFormat = "0"
Range(Range("N1"), Range("N1").End(xlDown)).NumberFormat = "0"
Range(Range("T1"), Range("T1").End(xlDown)).NumberFormat = "0"
Range(Range("O1"), Range("O1").End(xlDown)).NumberFormat = "m/d/yyyy"
Range(Range("U1"), Range("U1").End(xlDown)).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

Range(Range("G1:I1"), Range("G1:I1").End(xlDown)).NumberFormat = "$#,##0.00"

Range("C:D,J:M,F:F,P:P,S:T").EntireColumn.Hidden = True
Columns("E:E").EntireColumn.AutoFit
Rows("4:" & Rows.Count).EntireRow.AutoFit

End Sub

Sub Test3()
' works with the active worksheet

' determine how many rows of data there are
Dim lLR As Long
' if you have a fully populated column, say, column A
lLR = Range("A" & Rows.Count).End(xlUp).Row
'or, less reliable, possibly on the high side
lLR = ActiveSheet.UsedRange.Rows.Count

Range("A1:A" & lLR).NumberFormat = "0"
Range("B1:B" & lLR).NumberFormat = "0"
Range("D1:D" & lLR).NumberFormat = "0"
Range("F1:F" & lLR).NumberFormat = "0"
Range("N1:N" & lLR).NumberFormat = "0"
Range("T1:T" & lLR).NumberFormat = "0"
Range("O1:O" & lLR).NumberFormat = "m/d/yyyy"
Range("U1:U" & lLR).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

Range("G1:I" & lLR).NumberFormat = "$#,##0.00"

Range("C:D,J:M,F:F,P:P,S:T").EntireColumn.Hidden = True
Columns("E:E").EntireColumn.AutoFit
Rows("4:" & lLR).EntireRow.AutoFit

End Sub

Sub Test4()

' to ensure code applies to a specific worksheet
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
' change the sheet name in the line abobe as required

With ws
' determine how many rows of data there are
    Dim lLR As Long
    ' if you have a fully populated column, say, column A
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    'or, less reliable, possibly on the high side
    lLR = .UsedRange.Rows.Count
    
    .Range("A1:A" & lLR).NumberFormat = "0"
    .Range("B1:B" & lLR).NumberFormat = "0"
    .Range("D1:D" & lLR).NumberFormat = "0"
    .Range("F1:F" & lLR).NumberFormat = "0"
    .Range("N1:N" & lLR).NumberFormat = "0"
    .Range("T1:T" & lLR).NumberFormat = "0"
    .Range("O1:O" & lLR).NumberFormat = "m/d/yyyy"
    .Range("U1:U" & lLR).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    
    .Range("G1:I" & lLR).NumberFormat = "$#,##0.00"
    
    .Range("C:D,J:M,F:F,P:P,S:T").EntireColumn.Hidden = True
    .Columns("E:E").EntireColumn.AutoFit
    .Rows("4:" & lLR).EntireRow.AutoFit
End With 'ws

End Sub

Sub Test5()

' to ensure code applies to a specific worksheet
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
' change the sheet name in the line abobe as required

With ws
' determine how many rows of data there are
    Dim lLR As Long
    ' if you have a fully populated column, say, column A
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    'or, less reliable, possibly on the high side
    lLR = .UsedRange.Rows.Count
    
    Union(.Range("A1").Resize(lLR), _
          .Range("B1").Resize(lLR), _
          .Range("D1").Resize(lLR), _
          .Range("F1").Resize(lLR), _
          .Range("N1").Resize(lLR), _
          .Range("T1").Resize(lLR) _
                ).NumberFormat = "0"
    
    .Range("O1:O" & lLR).NumberFormat = "m/d/yyyy"
    .Range("U1:U" & lLR).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    
    .Range("G1:I" & lLR).NumberFormat = "$#,##0.00"
    
    .Range("C:D,J:M,F:F,P:P,S:T").EntireColumn.Hidden = True
    .Columns("E:E").EntireColumn.AutoFit
    .Rows("4:" & lLR).EntireRow.AutoFit
End With 'ws

End Sub

Regards, TMS

@Philb1: yep, no problem with the code working. Just highlighting the fact that you'd defined a variable for the Activesheet but then referred to the Activesheet rather than using the variable.

And there's little point using "With Ws ... End With" if you are going to qualify all the ranges with Ws. As in:

    With Ws
'
'   Currency Format
        Ws.Range(Ws.Cells(1, 7), Ws.Cells(BotRow, 9)).NumberFormat = "$#,##0.00"
'   Short Date Format
        Ws.Range(Ws.Cells(1, 15), Ws.Cells(BotRow, 15)).NumberFormat = "dd/mm/yyyy"
'
' etc
'
    End With
Exit Sub

End Sub

Incidentally, in this instance, rather than use:

Ws.Cells(1, 7)

you can get away with

Ws.Cells(1, "G")

Easier to understand in this context although not so good if you're cycling through columns and wish/need to use a column number.

Not trying to be picky, though I probably am, just making you aware for future reference.

Regards, TMS

Fair enough.

As far as the With Ws, it's me going over the top with qualification.

OK, but there's no point using With ... End With if you then fully qualify the range. It's meant a) to be a short-cut and b) to be more efficient because, as I understand it, it creates an internal variable that the .Range, .Cells, .Rows and .Columns refer to.

But I do understand your concerns.

Regards, TMS

@Philb1: thanks for the rep

@jhucker: If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Sorry Jess, not sure what you expect us to do about that. I tested my code variations and I'm pretty sure they work. I'm guessing Phil tested his code too. But, with no workbook, no worksheet, no data, only partial code, no error message and not even the line of code in need of debugging ... Maybe the worksheet is protected? Maybe you don't have a Sheet1?

Neither of these worked for me

doesn't even narrow it down to a specific module. Mine or Phil's or both? Same error? Which would be ...?

It's broke just don't hack it

Oh, and it's a week since we responded to you ... I've slept since then. Once or twice.