カテゴリー: ExcelVBA

コードコピー

'処理前の選択範囲の取得
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行に出来るコードをわざわざ分割するのは、遠回りをしているような気がするかもしれませんが、コードの可読性を上げるためには重要な考え方です

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

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

処理秒数	記事方法	都度選択
1回目	1.01953125	12.55078125
2回目	1.0078125	12.23046875
3回目	1.01171875	13.19921875
4回目	1.015625	13.2109375
5回目	1.0234375	12.33203125
6回目	1.02734375	12.234375
7回目	1.01171875	12.11328125
8回目	1.015625	12.421875
9回目	1.03515625	12.125
10回目	1.03515625	12.12109375

出来レースのように、完全に結果に差が出ることは分かり切った比較なので、特にコードは記載しません
概要としては、セル選択を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セルでもデータが接していれば、その範囲が広がります
また、取得されるのは範囲なので範囲内に空白があってもそのセルも取得されます

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

コードコピー

'シートの最終セルを選択する
Cells.SpecialCells(xlCellTypeLastCell).Select

シートの使用されている最終セルを取得するには「SpecialCells」メソッドに引数「xlCellTypeLastCell」を指定します

シートの最終セルとは、なんらかの設定の行われた最右下のセルです
入力値だけでなく書式設定などでも対象となります

ただ、使用時の注意点がありますので後述します

コード実行時の動き

Cells.SpecialCells(xlCellTypeLastCell).Select

コードを実行すると画像の様に書式設定されている最後のセルが選択状態になります

これを使用すれば、データベースの表に入力する際に最終行を調べるのが非常に簡単に行えます

Endメソッドによる移動は空白セルに影響をうける可能性がありますが、こちらは確実に最終行を取得することができますし、1行ステートメントなので処理も速いです

メソッドの注意点（未保存）

しかし、このメソッドは注意点がいくつかあります

まず、大きな注意点としてこのメソッドは最後に保存された状態の最後のセルを取得します

つまり、未保存の状態のセル範囲には対応できません

Cells.SpecialCells(xlCellTypeLastCell).Select
Range("C:D").Delete
Range("A1").Select
Cells.SpecialCells(xlCellTypeLastCell).Select

画像とコードを確認してください

まず、最初にメソッドを実行します
この時点では黄色セルの最終セルが選択状態になっています

その後に、C・D列を削除してA1セルをアクティブ状態に戻します
そして再度実行すると、今度は黄色セルではないセルが選択状態になっています

このセルは削除前に最後のセルとして認識されていたセルになります
削除を実行した後に保存が実行されていないため、最後のセルのデータも更新されていないため同じセルが選択されてしまっています

この動きがあるため、削除を実行するような処理の場合にメソッドを使用するには必ず先に保存を実行しておく必要があります

メソッドの注意点（書式設定）

次に、書式設定を行っただけで最後のセルの判定に含まれると解説しました
実際に黄色セルが選択されていることを確認しましたので間違いありません

しかし、ここでも注意しなければいけない動作をします

例えば、よくデータベースの表で列に入力するデータが固定のため
列をまるごと決まった書式設定を行うことがあります

その際に、列をまるごと選択して実行するか
範囲指定をして、予備範囲までを設定するかで変わります

コードが少し長いので、画像で確認してください

まず、最初にA列全体に対して「,」の表示形式を設定しています
その状態で最後のセルを取得するとB列の最後の行が選択されます

これが想定していた動きになります

次に、A列を削除してB列のデータをA列に移動します
そして、今度はA列全体ではなくA1からA10000までのセルに対して同じ表示形式の設定を行います
この状態で最後のセルを選択すると、B10000が選択されます

未保存であるためB列になる理由は解説しました
そして、A列の書式設定を行った範囲に対して最後のセルが認識されてしまう動きになっています

これは想定通りの動きではありません

つまり、行全体のように端を含むような書式設定はその行の標準設定となるため
使用済みセルと認識されることがなくなるわけです
列方向に関しても同様です

要は、セル全体に背景色を設定したとしてもセル全体が使用済みセルとして認識されるわけでは無い、ということです

列や行の選択を知らず、とりあえず途方もないセル範囲に予備の書式設定を行ってしまっているようなワークシートにはほんまに要注意です

こういった注意点を踏まえて考えると、データの取得のみを行う表データで最終セルを取得するには適していますが、加工を行う表で使用するには適していないかもしれません

その場合は、UsedRangeプロパティの方がいいかもしれません
それを使用した最終セルの選択方法は以下の記事にあります

選択範囲の最終セルを選択する

コードコピー

'使用済みのセルを範囲選択する
ActiveSheet.UsedRange.Select

シート内の使用済みセル範囲を選択するには「UsedRange」プロパティを使用します
これは、セル範囲のプロパティですがオブジェクトはシートになっていますので注意してください

コードの動きの解説

ActiveSheet.UsedRange.Select

例のコードを実行したときの動きです

コードを実行すると、B3からF8までが範囲選択されます

このプロパティは左上端と右下端の2点セルから範囲を取得します
その際、値が入力されているセルだけではなく、書式が設定されているだけでも対象となります

A列と1・2行目にデータは存在しないため、始点セルがB3になります
F8は黄色に背景色が設定されているだけですが、しっかり取得されています

イメージ的には、SpecialCells(xlCellTypeLastCell)メソッドに似ています
このメソッドはシート内の使用済みの最後のセル（右下端）を指定できるものです
このセルから始点は任意の箇所になりますが、同じように使用範囲を指定することができます

しかし、記事のプロパティでは保存の影響を受けません
保存を実行せずに最終セルを判定するには、こちらを使用することになります

ただ、範囲選択されてしまうので最終セルを保存しない状態で取得する場合は
この範囲の中から最後のセルを取得するような処理を作成する必要があります
その内容については以下の記事で解説を行っています

選択範囲の最終セルを選択する