【EXCEL VBA（マクロ）】指定した範囲の表示形式を標準にする

表示形式が標準にならない場合の対処方法
表示形式を標準にする
データ型の変換_標準に戻す

表示形式が標準にならない場合の対処方法

１．Range.NumberFormatを使用する方法

Range.NumberFormatプロパティを使用して、指定の列のセルの表示形式を設定できます。以下は、B列の数値を”0.00″形式で表示する例です。

Columns("B").NumberFormat = "0.00"

２．Range.Value2プロパティを使用する方法

Range.Value2プロパティを使用して、指定の列のセルの値を直接取得できます。以下は、B列の数値を文字列として取得する例です。

Dim cellValue As String
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
    cellValue = Range("B" & i).Value2
Next i

３．CStr関数を使用する方法

CStr関数を使用して、指定の列のセルの数値を文字列に変換することができます。以下は、B列の数値を文字列として取得する例です。

Dim cellValue As String
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
    cellValue = CStr(Range("B" & i).Value)
Next i

４．Format関数を使用する方法

Format関数を使用して、指定の列のセルの数値を指定された形式でフォーマットできます。以下は、B列の数値を”0.00″形式でフォーマットする例です。

Dim cellValue As String
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To lastRow
    cellValue = Format(Range("B" & i).Value, "0.00")
Next i

ループ処理の例

指定の列の全てのセルをループ処理し、セルの値が数値でない場合に、文字列中の特殊文字を削除して、前後の空白を削除し、表示形式を”General”に変更します。これにより、全てのセルが表示形式が標準になります。また、下記の例では2列目を処理するように指定していますが、必要に応じて処理する列を変更することができます。

Sub FixColumnFormat()
    Dim lastRow As Long
    Dim i As Long
    
    '処理する列の指定
    Dim columnToFix As Integer
    columnToFix = 2 '例として2列目を処理する場合
    
    '最終行の取得
    lastRow = Cells(Rows.Count, columnToFix).End(xlUp).Row
    
    'ループ処理
    For i = 1 To lastRow
        If Not IsNumeric(Cells(i, columnToFix).Value) Then
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, ",", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, ".", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, "-", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, "/", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, "\\", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, "(", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, ")", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Substitute(Cells(i, columnToFix).Value, "　", "")
            Cells(i, columnToFix).Value = Application.WorksheetFunction.Trim(Cells(i, columnToFix).Value)
            Cells(i, columnToFix).NumberFormat = "General"
        End If
    Next i
End Sub

表示形式を標準にする

Cells.ClearFormats

ThisWorkbook.ActiveSheet.Range("A1:G37").ClearFormats
ThisWorkbook.ActiveSheet.Columns("A").ClearFormats

Sub 標準の表示形式を設定する()
    For Each e In Selection
        e.Value = e.Value
    Next
MsgBox "完了"
End Sub

データ型の変換_標準に戻す

Sub ChangeCellFormatToStandard()
    Dim r       As Range        '// セル
    Dim f       As Boolean      '// 変換処理対象判定フラグ（True：処理対象、False：処理対象外）
    
    '// アクティブシートの入力セル範囲を１セルずつループ
    For Each r In ActiveSheet.UsedRange
        '// セルの書式が文字列ではない場合
        If r.NumberFormatLocal <> "@" Then
            '// 次セルの処理を行う
            GoTo CONTINUE
        End If
        
        '// 変換処理対象判定フラグを初期値として「処理対象外」に設定
        f = False
        
        '// セルの値が数値の場合
        If IsNumeric(r.Value) = True Then
            f = True
        '// セルの値が数式の場合
        ElseIf Left(r.Value, 1) = "=" Then
            f = True
        End If
        
        '// 変換処理対象の場合
        If f = True Then
            r.NumberFormatLocal = "G/標準"
        End If
        
CONTINUE:
    Next
    
    '// 入力セル範囲を再読み込み
    Call ReloadFormat

MsgBox "完了"
End Sub

【マクロ・VBA】指定した範囲の表示形式を標準にする