CODEWITHSUNDEEP
excelvba
Andre Nevares
Andre Nevares

Reputation: 741

How do I change number format to "General" for all cells?

I need to write a macro that apply NumberFormat = "General" to all cells in a spreadsheet.

This is what I have

Sub set_general_format()
    Select Case _
        MsgBox("Do you want to apply generic to all cells?" _
        & "Save before?", vbYesNoCancel, _
        "Or just change to numeric?")
        Case Is = vbYes
            ThisWorkbook.Save
        Case Is = vbCancel
            Exit Sub
    End Select

    Sheets(1).Select
    
    For i = 1 To Sheets.Count
        Sheets(i).Select
        Cells.Select
        Selection.NumberFormat = "General"
        Application.CutCopyMode = False
    Next i
End Sub

Can someone help me to improve this code?

Upvotes: 0

Views: 325

Answers (2)

user3259118
user3259118

Reputation:

Replace all of:

Sheets(1).Select
    
    For i = 1 To Sheets.Count
        Sheets(i).Select
        Cells.Select
        Selection.NumberFormat = "General"
        Application.CutCopyMode = False
    Next i

With:

    For Each Sheet In ThisWorkbook.Sheets
        Sheet.Cells.NumberFormat = "General"
    Next

Upvotes: 0

Simon Sultana
Simon Sultana

Reputation: 243

You can combine the three lines:

Sheets(i).Select
Cells.Select
Selection.NumberFormat = "General"

in one line as follows:

Sheets(i).Cells.NumberFormat = "General"

Upvotes: 1

Related Questions