make is.dev make it simple. development.
2024年11月29日

範囲操作

EXCEL VBAでの範囲操作で、よく使うものチートシート。

使用環境

・Windows11
・EXCEL 2019

選択

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("シート名")

' 範囲を指定して選択
ws.Range("A1:E5").Select

' 範囲を左上と右下のセルで指定して選択
ws.Range(ws.Cells(1, 1), ws.Cells(5, 5)).Select

' 複数の範囲を指定して選択
ws.Range("A1:E5,A6,A7:E7").Select

' 離れた複数の範囲を左上と右下のセルで指定して選択
Union(ws.Range(ws.Cells(1, 1), ws.Cells(5, 5)), ws.Range(ws.Cells(6, 1), ws.Cells(6, 1))).Select
VBA

範囲を選択する場合、シートがアクティブ状態にないとエラーになるので注意。

UnionはRangeを統合して一つのRangeにするメソッドです。

名前定義の範囲選択

ThisWorkbook.Worksheets("シート名").Range("[名前定義名]").Select
VBA

範囲を選択する場合、シートがアクティブ状態にないとエラーになるので注意。

アクティブ範囲取得

Dim range1 As Range
Set range1 = Selection
VBA

指定範囲取得

Dim range1 As Range
Set range1 = ThisWorkbook.Worksheets("シート名").Range("A1:E5")
VBA

範囲内のセル毎操作

Dim cell As Range

For Each cell In Worksheets("シート名").Range("A1:E5").Cells
    Debug.Print cell.Value
Next
VB

範囲をループで回してセル毎に処理する。
セルに対する操作は以下を参考。

セル操作

値設定

Worksheets("シート名").Range("A1:E5").Cells.value = "sample"
VBA

セルに対しての値設定と同じ。
範囲内のセル全てにまとめて値を設定する。

計算式設定

Worksheets("シート名").Range("A1:E5").Cells.value = "=1+1"
VBA

セルに対しての値設定と同じ。
範囲内のセル全てにまとめて式を設定する。

ただし、式の中にセル参照がある場合は注意が必要。

Worksheets("シート名").Range("A2:E5").Cells.value = "=A1+1"
VBA

起点になるセル（上記例ではA2）は指定した式のまま入るが、他セルには行列が自動的にずれた参照が入る。
例を挙げると、上記例ではB3には「=B2+1」が入ることになる。

フォント、文字サイズ設定

With ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font
    .name = "Yu Gothic"
    .Size = 11
End With
VBA

セルに対してのフォント、文字サイズ設定と同じ。
範囲内のセル全てにまとめてフォント、文字サイズを設定する。

縦横表示位置指定

With ThisWorkbook.Worksheets("シート名").Range("A1:E5")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
VBA

縦位置はVerticalAlignmentで指定する。
上（xlTop）、中央（xlCenter）、下（xlBottom）。

横位置はHorizontalAlignmentで指定する。
指定なし（xlGeneral）、左（xlLeft）、中央（xlCenter）、右（xlRight）。

文字色設定

' ThemeColor（1～10）で指定する
' TintAndShadeは -1～1の範囲で指定。中間色は0。
' UIでの色選択時の値は上から、0、0.8、0.6、0.4、-0.25、-0.5
With ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font
    .ThemeColor = 2
    .TintAndShade = 0.8
End With

' ColorパレットのIndex（1～56）で指定する
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.ColorIndex = 3

' RGBで指定する
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Color = RGB(255, 0, 0)
VBA

セルに対しての文字色設定と同じ。

ThemeColor、ColorIndex、Color、いずれも指定するとTintAndShadeは0に戻る。
そのため、TintAndShadeで明るさ調整する場合はその後で指定する必要がある。

セル内のテキストの一部のみ色を付ける場合は以下のように指定する。

' セル全体の色指定
With ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font
    .ThemeColor = 5
    .TintAndShade = 0.25
End With

' 部分色指定
With ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=3, Length:=2).Font
    .ThemeColor = 4
    .TintAndShade = 0
End With
VBA

部分的な指定は、VBAで値を書き変えるとリセットされるので注意。

背景色設定

' ThemeColor（1～10）で指定する
' TintAndShadeは -1～1の範囲で指定。中間色は0。
' UIでの色選択時の値は上から、0、0.8、0.6、0.4、-0.25、-0.5
With ThisWorkbook.Worksheets("シート名").Range("A1:E5").Interior
    .ThemeColor = 4
    .TintAndShade = 0.25
End With

' ColorパレットのIndex（1～56）で指定する
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Interior.ColorIndex = 3

' RGBで指定する
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Interior.Color = RGB(255, 0, 0)
VBA

基本的には上記のいずれかで良い。

ただ、パターンが指定されている場合はこれだけではパターンが外れないので、以下を追加する。

With ThisWorkbook.Worksheets("シート名").Range("A1:E5").Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .PatternTintAndShade = 0
End With
VBA

表示形式設定

' 標準に戻す
ThisWorkbook.Worksheets("シート名").Range("A1:E5").NumberFormatLocal = "G/標準"

' 数値
ThisWorkbook.Worksheets("シート名").Range("A1:E5").NumberFormatLocal = "0_ "

' 文字列
ThisWorkbook.Worksheets("シート名").Range("A1:E5").NumberFormatLocal = "@"

' 通貨
ThisWorkbook.Worksheets("シート名").Range("A1:E5").NumberFormatLocal = "\#,##0;\-#,##0"

' 日付
ThisWorkbook.Worksheets("シート名").Range("A1:E5").NumberFormatLocal = "yyyy/m/d"
VBA

上記は一例。
設定値はUIからの書式指定のユーザー定義の設定内容を指定する形になるので、UIから設定値を作ってそれをコピー＆ペーストするのが分かりやすい。

太字、斜体、下線、取り消し線

' 太字　設定／解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Bold = True
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Bold = False

' 斜体　設定／解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Italic = True
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Italic = False

' 下線、二重下線　設定／解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Underline = xlUnderlineStyleSingle
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Underline = xlUnderlineStyleDouble
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Underline = xlUnderlineStyleNone

' 取り消し線　設定／解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Strikethrough = True
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Font.Strikethrough = False
VBA

セル内のテキストの一部のみ設定／解除する場合は以下のように指定する。

' 太字　設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=2, Length:=2).Font.FontStyle = "太字"

' 斜体　設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=2, Length:=2).Font.FontStyle = "斜体"

' 太字＋斜体　設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=2, Length:=2).Font.FontStyle = "太字 斜体"

' 下線、二重下線　設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=3, Length:=2).Font.Underline = xlUnderlineStyleSingle
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=3, Length:=2).Font.Underline = xlUnderlineStyleDouble

' 取り消し線　設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=2, Length:=2).Font.Strikethrough = True


' 太字／斜体　解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=2, Length:=2).Font.FontStyle = "標準"

' 下線、二重下線　解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=3, Length:=2).Font.Underline = xlUnderlineStyleNone

' 取り消し線　解除
ThisWorkbook.Worksheets("シート名").Range("A1:E5").Characters(Start:=2, Length:=2).Font.Strikethrough = False
VBA

罫線

Dim cell As Range
Set cell = ThisWorkbook.Worksheets("シート名").Range("A1:E5")

' 上に実線を引く
With cell.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
End With
VBA

Bordersの引数で対象の罫線を指定する。
上（xlEdgeTop）、下（xlEdgeBottom）、左（xlEdgeLeft）、右（xlEdgeRight）。
左上から右下への斜線（xlDiagonalDown）、左下から右上への斜線（xlDiagonalUp）。
範囲内上下の中央線（xlInsideHorizontal）、範囲内左右の中央線（xlInsideVertical）。
上下左右は範囲外側の罫線になるので注意が必要。

LineStyleのパラメータで、線の種類を指定する。
なし（xlNone）、実線（xlContinuous）、破線（xlDash）、点線（xlDot）、など。

Weightのパラメータで、線の太さを指定する。
極細（xlHairline）、細（xlThin）、中（xlMedium）、太（xlThick）。

色は前述の文字色などと同様で、ColorIndex、ThemeColor、ColorとTintAndShadeで指定する。

書式等のリセット

ThisWorkbook.Worksheets("シート名").Range("A1:E5").ClearFormats
VBA

フォント、文字サイズ、表示形式、太字、斜体、下線、文字色、背景色、罫線など一通りがリセットされる。

ただし、セル内のテキストの一部を太字や斜体、文字色などを設定している場合は、そこは解除されないので注意が必要。

複製

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("シート名")

ws.Range("A1:E5").Copy ws.Range("A7")
VBA

複製先の指定は、起点（左上）のセルを指定すると必要な範囲で複製される。

Copyメソッドを使うとクリップボードの内容が書き換わるので注意。

高さ　取得／設定

' 高さ取得
Dim height As Double
height = ThisWorkbook.Worksheets("シート名").Range("A1:E5").rowHeight

' 高さ設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").rowHeight = 40
VBA

事実上、行の高さ取得と設定になる。

範囲に複数行含まれる場合の高さ取得では、高さが一律でないとNullが返却されるので注意。

高さの合計が欲しい場合は、以下で合計を取得することも可能。

Dim height As Double
height = ThisWorkbook.Worksheets("シート名").Range("A1:E5").height
VBA

横幅　取得／設定

' 横幅取得
Dim width As Double
width = ThisWorkbook.Worksheets("シート名").Range("A1:E5").ColumnWidth

' 横幅設定
ThisWorkbook.Worksheets("シート名").Range("A1:E5").ColumnWidth = 40
VBA

事実上、列の横幅取得と設定になる。

範囲に複数列含まれる場合の横幅取得では、幅が一律でないとNullが返却されるので注意。