カテゴリー: ExcelVBA

'2行目以降を全て選択する
Cells. _
Resize(Cells.Rows.Count - (Range("A2").Row - 1), Cells.Columns.Count). _
Offset(Range("A2").Row - 1, 0). _
Select

表範囲の中で、見出し（見出しを1行目だけとした場合）以外の行を取得します
見出し以外を全て新たなデータに置き換える時にデータ範囲を全て初期化する時に使用します
なお、解説の為に改行しています

画像のような表範囲の中で、1行目が見出しで2行目以降がデータ範囲である場合にコードを使用することで見出し以外の範囲を全て選択することができます
少し見づらいですが、左下のウォッチ式の中の値がA1から行範囲にアドレスが変化している部分を確認してください

また、少し応用すると空白を除いた純粋なデータ範囲のみを取得する事も可能です

コード解説

Cells. _

何よりまずは、セル全体を指定しますのでCellsオブジェクトに対して処理を行います
Cellsオブジェクトは引数を省略した場合はセル全体が取得されます

Resize(Cells.Rows.Count - (Range("A2").Row - 1), Cells.Columns.Count). _

次に、Resizeプロパティを使用して範囲サイズの変更を行います
このプロパティは引数に指定した数値のセル範囲を取得するプロパティです

この際に指定する数値は、1つ目の引数が行数になります

(Cells.Rows.Count – (Range(“A2”).Row – 1),
この部分が1つ目の引数の部分になります、ここで行数を指定します
ここでは、A2セルを起点にするので2行目ということになります
2行目から取得を行うということは、除外するのが1行目の1行だけということになりますので、セルの全行数から1を引くことで指定することができます

とはいえ、その数値は固定では2行目以降しか選択できなくなってしまいますので可変にしておいたほうが便利です
そこでRangeオブジェクトのRowプロパティを使用して指定セルの行数を取得します
その行数から、-1します
これは、除外する行数の指定なので指定セルは除外対象になりません
なので除外する行数から-1してあげることで、指定セルの1行上が指定できます

Cells.Columns.Count)
2つ目の引数は列数の指定になりますが、今回は全ての範囲を指定するのでCellsオブジェクトに含まれている列数を指定します
これで全ての列の指定になります

これで、取得するべきセル範囲に変更することが出来ました

Offset(Range("A2").Row - 1, 0). _

Reiszeプロパティは、指定セル範囲のサイズを変更しますが、左上からのサイズ変更になります

つまり、上のコードで変更した行数では範囲の下から行数が削られていきます
なので、このままでは1行目が除外されるわけでは無く、最下行が除外された状態になります

そこでOffsetプロパティを使用して範囲を移動させます
Offsetプロパティは引数に指定した数値分、セル範囲を移動させます

今回は行方向への移動を行いますので、引数の1つ目に移動数を指定します
この移動値に関しては、Resizeプロパティで使用したものと同じになります

Select

最後に、この取得したセル範囲を選択状態にします
ここのメソッドを変更すれば、他の操作も可能になります

メソッドを使用せずに変数に取得させて、これらの範囲に対して色々な操作を行うことも出来ます

'A2の下にある空白セルを選択する
Dim セル As Range
Set セル = Range("A2")

Do Until セル = ""
Set セル = セル.Offset(1, 0)
Loop
セル.Select

セル選択を減らすためには、RangeオブジェクトをForEachループで処理を行います
ただその処理は事前にセル範囲が分かっている場合に限定されます

今回の記事にある、空白のセルになるまで処理を繰り返す
というような、セル範囲ではなくセル単体を検証していくようなループではDoループで処理を行います

そして、その際にセルを選択することを無くすためには、コードにあるRange.OffsetプロパティやCellsオブジェクトなどで整数値を増減させて移動させます

この方法でセル選択は無くすことが出来るので、処理速度の向上は図れています

ただ、この方法での面倒な場合があります
それは別のシートやブックのセルを処理したい場合です
この場合には、シート名から指定する必要があり、コードが冗長になります
Withステートメントを使用する方法もいいのですが、あまり処理行が長い場合にはコードの可読性はあまり高くありません

そこで使用したいのが記事コードです

コードの解説

Dim セル As Range
Set セル = Range("A2")

Do Until セル = ""
Set セル = セル.Offset(1, 0)
Loop
セル.Select

このコードではセルを変数に代入して、それを更新していくことでセルの移動を行うループ方法です

画像のコードの動きを確認してください
最初にA2セルが代入されて、ループ処理により検証取得されたセルを最後に選択しています、A2から下方向に空白のセルを検索しているので、A4が選択されて終了しています

Dim セル As Range
Set セル = Range("A2")

まずはセル変数の宣言と、初期位置の取得です
A2セルを取得させることで、この変数での初期位置とします

Do Until セル = ""

・・・

Loop

取得したセルが空白になるまでループを繰り返します
ただ、当然このままでは永遠にA2セルを参照し続けるので無限ループです

Set セル = セル.Offset(1, 0)

ここでセルを更新しています
セル変数に代入するセルは、その変数自体を指定することができます

このセル変数からOffsetを使用して1つ下のセルを代入させます

セル変数自体は、すでにブックやシートを含めたセルが取得されていますのでアクティブブックやシートである必要もありません

そして、ここでの代入によって次のセルが参照対象になるわけですが、セルへの参照が切り替わるだけでセル選択に関しては行っていませんので、メモリ上で処理が実行されています

なので整数型を使用した移動処理と差は感じられることは無いと思います

セル.Select

空白のセルが見つかったら、セルの更新が行われずにループを抜けるので
ループ後にそのセル変数を操作することが出来ます

別のシートのセル範囲を操作したいことは多いので、この方法を使用すればコードが非常にすっきりするので試してみてください

'処理前の選択範囲の取得
Application.Goto Selection

’取得したセル範囲を選択
Application.Goto

処理を実行する時にどうしてもセル選択を行う必要があり、しかも処理実行後にはもともと選択していたセル範囲に選択を戻しておきたい

そんな場面があります、そんな時に使用するのがこのコードです

コード解説

Application.Goto Selection

Range("A100:A103").Select
Range("B100:B103").Select
Range("C100:C103").Select

Application.Goto

記事コードを上記の様に変更しました
選択範囲を変更して処理実行前の選択範囲に戻します

Application.Goto Selection

Application.Gotoメソッドを使用して、最初にもともとのセル範囲を再指定します

ここは正直、引数にSelectionを指定する必要はありません
このSelectionはコードでいうRange(“A100:A103”)を指定しても構いません

コードの可読性の為にしているだけですね

SelectionでもRange指定であっても、このコードが実行された時点で、もともと選択されていたセル範囲がApplication.Gotoメソッドに保持されます

Range("A100:A103").Select
Range("B100:B103").Select
Range("C100:C103").Select

そのあとで、適当に処理を実行してセル選択を別の範囲にします
この時、選択範囲の変更は何度行っても問題ありません

Application.Goto

処理を実行して、またもともとの選択範囲に戻したい場合はApplication.Gotoメソッドの引数Referenceを省略して指定することで、もともとの選択範囲が指定されます

2つ目の引数ScrollをTrueに指定すれば、もともとの選択範囲を表示内の左上に表示させます

また、このメソッドは直前に保持した分のみしか保持できませんので
処理の中でApplication.Gotoメソッドを使用したい場合は利用できません

その場合はもともとの選択範囲をRange型のObject変数に代入して保持しておく方法で行います
あまりないとは思いますが・・・

'「Book1」の「Sheet1」のA2セルを選択する
Application.Goto Workbooks("Book1").Worksheets("Sheet1").Range("A2")

ブックやシートの選択をセルの選択と同時に行うには、Application.Gotoメソッドを使用します
このメソッドを使用すると、セルの選択を指定すると同時にブックやシートの選択も行います

RangeオブジェクトのSelectメソッドでもセル選択を行うことができます
ですが、アクティブではないブックやシートのセルを指定するとエラーが発生します

Scrollは、移動先のセルの表示に関する設定です
この引数にTrueを指定すると、セル範囲の左上を表示範囲の左上に合わせます
省略した場合は、Falseが指定されるので画面内であれば画面がスクロールしません

コード解説

Application.Goto Workbooks("Book1").Worksheets("Sheet1").Range("A2")

このコードでは、別のブックから指定のシートのセル選択を行います
Book1のSheet1のA2のセルを選択しています

画像の動きを確認してください
特にシートのタブの部分に注目してください

シートのタブの見出しが変わっていることが変わると思います
ここから別のブックにアクティブが切り替わっていることが分かります

その後、A2セルが選択状態になっていることが分かります

Application.Goto Worksheets("Sheet1").Range("A2")

この様に、ブックは指定せずにシートから指定すればアクティブブックのシート間の移動が可能です
さらに、シートの指定もせずにRangeのみの指定を行えば、アクティブシートのセル選択を行います

Application.Goto Range("B3"), True

引数2つ目の、ScrollにTrueを設定したコードです
シートの指定も行っていませんので、アクティブシートのセル選択になります

画像の動きを確認してください
A1セルからB3セルへ選択が移動しています

ウィンドウ上ではセルが移動していないので、パッと見て分かりにくいですが
B3セルが左上に来ています

表の作成などを行って、最終的にその表を表示画面に綺麗に表示させる場合などに使用するといいかもしれません
通常の処理上では、ウィンドウ上での動きが分かりにくいので使用することは無いかもしれません

処理高速化に大きな効果があるのが、セル選択を減らす事です

しかし、それを実現するために1行のコードが長くなる場合があります
処理速度の向上に繋がりますが、恐ろしく可読性が低下する可能性があります

処理速度よりも可読性の方が圧倒的に重要です

でも、処理速度だって重要です

そんな時に使う方法として、1行を短くしつつセルは選択しないようにする方法です
考え方は簡単で、変数に順番に代入させていき
最終的に確定したセル範囲を選択するようにします

こうすると、可読性を保ちつつセル選択の回数を減らすことが出来ます

使用例

Dim 取得用Range As Range
Set 取得用Range = Range("A1").CurrentRegion
Set 取得用Range = 取得用Range.Cells(取得用Range.Cells.Count)
Set 取得用Range = Range(Range("A2"), 取得用Range)
Set 取得用Range = Intersect(Range("B1").EntireColumn, 取得用Range)
取得用Range.Select

実際に動きを確認しましょう、今回作成したコードが以上のものです
処理の動きとしては、表の指定列の見出しを含まないデータ範囲です

画像の選択された範囲を取得するのが、目標です
よくあるパターンの取得例やと思います

Dim 取得用Range As Range

まずは、最初に変数に代入させてセル選択を行うため、それを可能にするためにRange型変数の宣言を行います
これ以降は、Rangeオブジェクトでの指定を行わずにこの変数名で行います

Set 取得用Range = Range("A1").CurrentRegion

最初に表全体の範囲を取得します
ここから順番に部分範囲を取得していきながら、必要な部分のみに除外していきます
表全体の取得にはCurrentRegionプロパティを使用して取得します
セルA1を含む空白セルに囲まれた、表範囲が取得されます

この時点から、変数にセルを代入しています

Set 取得用Range = 取得用Range.Cells(取得用Range.Cells.Count)

次に、取得したセル範囲の中での最後のセルを取得します
これは最終行を取得したいためです

基本的に表という仕様では、見出しからデータの開始までの範囲が変化することはほぼありません
画像でいうところの、1行目が見出しで2行目以降がデータ範囲である
という、大前提の話です

つまり、最終行が取得できれば2行目から指定することでデータ範囲行を特定することができる訳です

この最終行の取得には色々な方法がありますので、これが最善とは言いませんが
表範囲という前提で考えると、Endプロパティでも良いのですが取得したい列が常にデータで埋められている前提である必要があります

しかし、この記事コードで取得すればデータ範囲に空白があってもちゃんと表範囲として取得することが出来ますので、汎用的なコードかなと思います

Set 取得用Range = Range(Range("A2"), 取得用Range)

ここで、始点セルまでを範囲取得しています
この時点で表範囲の見出しを除いたデータ範囲が取得された状態になります

これには２つ方法があり、Resizeプロパティを使用する方法と最後のセルから始点セルまでを範囲取得する方法です

今回は、後者の方法を採用します
動きがこっちのほうがイメージしやすいような気がしたので

もし、Resizeプロパティを使用する場合は、表範囲を取得してからすぐにこのプロパティで見出し行を除外してあげればいいだけですね
これに関しては、ここでは割愛します

Set 取得用Range = Intersect(Range("B1").EntireColumn, 取得用Range)

ここで最後に表範囲内の指定の列を取得します
Intersectメソッドを使用して、指定列との重複範囲を取得しています

引数では、2つありますが1つ目の引数では、指定列を指定しています

Range("B1").EntireColumn

EntireColumnプロパティは、指定したセルを含む列全体を取得するプロパティです、なのでここではB1セルを含む列、つまりB列が取得される形になります

このB列とここまでで取得した配列変数（表範囲のデータ範囲のみ）の重複する範囲、となるのでデータ範囲のB列が取得されます

取得用Range.Select

ここまでで取得が完了したので、変数セルを選択状態にして完了です
選択せずに、この変数セルに値を入力することもできます

また、この範囲をFor Eachループで1セルずつ処理を行うこともできます

Rangeオブジェクトなので、さまざまな用途に使用できますので、いろいろな応用方法を考えてみてください

Intersect(Range("B1").EntireColumn, Range(Range("A2"), Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Cells.Count))).Select

ちなみに、これらを1行で納めると上記のような形になります
恐ろしく長いコードになり、とても可読性の良いコードとは言えません

1行に出来るコードをわざわざ分割するのは、遠回りをしているような気がするかもしれませんが、コードの可読性を上げるためには重要な考え方です

可読性を維持しつつ、処理速度も向上させる考え方です

最後に、このコードの動きを順番にセル選択をした場合と、記事のように変数で選択した場合との処理時間の差です

出来レースのように、完全に結果に差が出ることは分かり切った比較なので、特にコードは記載しません
概要としては、セル選択を1000回実行した場合の処理時間を10回計測したものです

いまさらですが、セル選択を少なくするだけでこれだけの処理速度向上につながるということですね

ごくごく当たり前のことですが、変数に代入させるものは変数も使用できる
そんな方法を利用する考え方をしてもらいたい、という話でした

'表内のA列のセルだけを選択状態にする
Intersect(Range("A1").CurrentRegion, Range("A1").EntireColumn).Select

表の特定の列をリストとして取得したい場合に使用するコードです

入力用のコンボボックスや、リストボックスなどのリストデータであったり
複数列を参照して特定のデータと一致するものに対応するデータを取り出す、いわゆるVlookup関数のように使用することができます

この場合に便利なのが、CurrentRegionプロパティとIntersectメソッドを組み合わせて取得する方法です

コードの解説

Intersect(Range("A1").CurrentRegion, Range("A1").EntireColumn).Select

1行で指定出来るために1行ステートメントとしていますが、分かりにくければ分割して順番に選択してもいいと思います
ループで大量に使用しなければ処理速度には影響しません

まず、この1行で実行した時の動きの確認です

A列の表範囲が選択されます

この時、表範囲なので最終行も同時に取得出来ています
これで、表全体ではなく特定の列のみを選択状態にする事が出来ます

次に、このコードを分割して解説していきます

Intersect(～～).Select

最初に出てくるのが、この部分です

これはIntersectメソッドです
引数内のセル範囲で重複した範囲を取得するメソッドです
これを使用することで、表範囲とA列の重複する範囲を取得することが出来ます

～(Range("A1").CurrentRegion, ～

次に出てくるのが、CurrentRegionプロパティです

このプロパティは、指定セルを含む表範囲を取得します
この時点で表全体が取得されます

～ Range("A1").EntireColumn)～

ここでは、EntireColumnプロパティを使用しています

このプロパティは、指定セルを含む列全体を取得します
これによりA列全体が取得された状態になります

なので、日本語にすると
A1セルを含む、表全体とA列全体の重複した範囲を選択する
と、いう形になります

なぜか日本語にしても分かりにくいのが不思議ですが、まあそうゆうことなんです

引数の2つ目のEntireColumnプロパティの指定セルアドレスをB1にすれば、B列を取得することが出来ます

1つ目はB1にしても、しなくてもどちらでも構いません

'表範囲の最後のセルを選択する
Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Cells.Count).Select

最後のセルを選択するにはSpecialCells(xlCellTypeLastCell)メソッドを使用しますが、このメソッドは保存されていない状態で削除を行った場合はズレが生じることがあります

しかし、このメソッドでは表範囲としての最終セルを指定するので、その条件に影響を受けません

特にシートに集計表を作成する際に、1度シートのセルを全削除してから作成するような処理の際にはとても有効です

ただ、画像の動きを確認してもらうと分かりますが、このプロパティは書式設定は考慮しないため、そこがSpecialCells(xlCellTypeLastCell)メソッドとの大きな違いになりますが、表形式の特性上あまりそういった状況は無いと思います

状況に合わせて使用できる場合は使用するような形になります

'指定セルを含むセル範囲の取得
Range("A1").CurrentRegion.Select

指定のセルを含む表範囲を取得するには「CurrentRegion」プロパティを使用します
表範囲とは、そのセルを含むデータの入力された連続セル範囲です

要は、表内のセルを指定するとその表全体が取得されます

空白の行と列があるとそこで取得を終了するので、表の全体を指定する際に便利なメソッドです
空白は表全体が空白と接している所が対象となるので、データのどこか1セルでもデータが接していれば、その範囲が広がります
また、取得されるのは範囲なので範囲内に空白があってもそのセルも取得されます

このメソッドは表に罫線を引いたりするような表全体を対象とする場合にとても有効です