－ちょっと使えるネタを紹介－
Excel VBAで表全体のセル範囲を取得する方法について、ご紹介します。表のセル範囲を取得するには、CurrentRegionがかなり便利です。ただ、空白行が弱点ですので、代替案として、Endを使って、表のセル範囲を取得する方法についてご紹介します。
この記事では、表全体のセル範囲を取得する方法についてご紹介します。
表全体のセル範囲を取得するには、『CurrentRegion』がかなり便利です。
表全体のセル範囲を取得できれば、1行目だけとか、最終列だけとかを取得することができます。
ただ、CurrentRegionには、空白行という弱点がありますので、その場合の代替案についてもご紹介します。
では、表全体のセル範囲を、取得する方法について解説してきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
初めに、ポイントとなるVBAコードを記載しておきます。
VBAコードだけ確認したい場合に、ご活用ください。
'CurrentRegionでセル範囲を取得 With Range("A1").CurrentRegion .Select '表の範囲を選択 .Rows(1).Select '表の1行目を選択 .Columns(1).Select '表の1列目を選択 .Rows(.Rows.Count).Select '表の最終行を選択 .Columns(.Columns.Count).Select '表の最終列を選択 .Rows(2 & ":" & .Rows.Count).Select '表の値のみを選択 MsgBox .Rows(.Rows.Count).Row '表の最終行の行番号を取得 MsgBox .Columns(.Columns.Count).Column '表の最終列の列番号を取得 End With '空白行がある場合の代替案（UsedRangeを使う） ActiveSheet.UsedRange.Select '空白行がある場合の代替案（Endを使う） Dim A '最終行を取得 A = Cells(Rows.Count, "A").End(xlUp).Row '表のセル範囲を取得 Range(Range("A1"), Cells(A, "C")).Select
では、解説していきます。
VBAで表全体のセル範囲を、簡単に取得する方法で、『CurrentRegion』を使います。
CurrentRegionは、セル範囲を取得する方法として、かなり使いますので、覚えておいて損はないです。
では、CurrentRegionの使い方を、具体的なVBAコードを使いながら、解説していきます。
まず、CurrentRegionの使い方です。次のように入力します。
『Range("A1").CurrentRegion』
Rangeには、セルに入力されている表の左上のセルを指定します。
これだけで、表全体のセル範囲を取得できます。
次のような表で試してみます。
表全体のセル範囲を取得してみます。
Sub TEST1()
'表のセル範囲を選択
Range("A1").CurrentRegion.Select
End Sub
実行してみます。
表全体が、選択されました。
表の位置が変わったときについて、CurrentRegionを使ってみます。
表の位置を変えてみました。
この表のセル範囲を全て取得してみます。
やり方は、Rangeで指定するセルを変えるだけです。
Sub TEST2()
'表のセル範囲を選択
Range("B2").CurrentRegion.Select
End Sub
表の始まりが、『B2』から始まっているので、『Range("B2")』としています。
実行してみます。
表全体のセル範囲を取得できました。
表の大きさを変えても、同じVBAコードで表全体を取得できます。
表の大きさを変えてみました。
この表のセル範囲を、CurrentRegionで取得してみます。
VBAコード自体は、先ほどと同じです。
Sub TEST3()
'表のセル範囲を選択
Range("A1").CurrentRegion.Select
End Sub
実行してみます。
表全体のセル範囲を取得できました。
VBAコード自体は、同じです。
『CurrentRegion』、かなり便利です。
つづいて、CurrentRegionで取得したセル範囲から、一部のデータを取得する、というのをやってみます。
表全体のセル範囲ではなく、表の一部を取得したい場合もありますので、参考になるかと思います。
CurrentRegionで取得したセル範囲から、表の1行目を取得したい場合です。
VBA関数の『Rows』を使います。
次のようなVBAコードで取得できます。
Sub TEST4()
'表の1行目を選択
Range("A1").CurrentRegion.Rows(1).Select
End Sub
実行してみます。
表の1行目だけを取得できました。
CurrentRegionで取得したセル範囲から、表の1列目を取得したい場合です。
VBA関数の『Columns』を使います。
次のようなVBAコードで取得できます。
Sub TEST5()
'表の1列目を選択
Range("A1").CurrentRegion.Columns(1).Select
End Sub
実行してみます。
表の1列目だけを取得できました。
CurrentRegionで取得したセル範囲から、表の最終行を取得したい場合です。
VBA関数の『Rows』を使います。
最終行は、『オブジェクト.Rows.Count』で取得できます。
次のようなVBAコードで取得できます。
Sub TEST6()
'表の最終行を選択
With Range("A1").CurrentRegion
.Rows(.Rows.Count).Select
End With
End Sub
実行してみます。
表の最終行を取得できました。
CurrentRegionで取得したセル範囲から、表の最終列を取得したい場合です。
VBA関数の『Columns』を使います。
最終列は、『オブジェクト.Columns.Count』で取得できます。
次のようなVBAコードで取得できます。
Sub TEST7()
'表の最終列を選択
With Range("A1").CurrentRegion
.Columns(.Columns.Count).Select
End With
End Sub
実行してみます。
表の最終列を取得できました。
CurrentRegionで取得したセル範囲から、表の値だけを取得したい場合です。
VBA関数の『Rows』を使います。
最終行は、『オブジェクト.Rows.Count』で取得できます。
次のようなVBAコードで取得できます。
Sub TEST8()
'表の値だけを選択
With Range("A1").CurrentRegion
.Rows(2 & ":" & .Rows.Count).Select
End With
End Sub
実行してみます。
表のデータから値だけを取得できました。
CurrentRegionで取得したセル範囲から、最終行の行番号を取得したい場合です。
VBA関数の『Rows』を使います。
最終列は、『オブジェクト.Columns.Count』で取得できます。
最後に、行番号を『Row』で取得します。
次のようなVBAコードで取得できます。
Sub TEST9()
'表の最終行の行番号を取得
With Range("A1").CurrentRegion
MsgBox .Rows(.Rows.Count).Row
End With
End Sub
実行してみます。
最終行の行番号を取得できました。
CurrentRegionで取得したセル範囲から、最終列の列番号を取得したい場合です。
VBA関数の『Columns』を使います。
最終列は、『オブジェクト.Columns.Count』で取得できます。
最後に、行番号を『Column』で取得します。
次のようなVBAコードで取得できます。
Sub TEST10()
'表の最終列の列番号を取得
With Range("A1").CurrentRegion
MsgBox .Columns(.Columns.Count).Column
End With
End Sub
実行してみます。
最終行の列番号を取得できました。
セル範囲から取得するデータは、こんな感じでいろいろ取得することができます。
表のデータに空白の行があると、CurrentRegionでセル範囲を取得できません。
これは、ちょっと注意が必要です。
では、表のデータに空白の行がある場合で、CurrentRegionを使って表のセル範囲を取得してみます。
使う表はこんな感じです。
空白行がありますね。
では、CurretRegionでセル範囲を取得してみます。
Sub TEST11()
'表のセル範囲を選択
Range("A1").CurrentRegion.Select
End Sub
実行してみます。
空白の行以降のセル範囲が取得できていません。
でもまぁ、表なので空白行があるというのは、あまりないかと思います。
ただ、知っておけば、CurrentRegionでおかしな挙動をした時に、対策を立てやすくなります。
空白行がある場合の対策としては、『UsedRange』を使う方法です。
では、空白行がある表で『UsedRange』を使ってセル範囲を取得してみます。
Sub TEST12()
'使用しているセル範囲を選択
ActiveSheet.UsedRange.Select
End Sub
実行してみます。
表のセル範囲を取得できました。
ただし、UsedRangeは問題点があります。
UsedRangeは行の高さを変えたり、罫線があったとしても、その範囲を認識してしまって、取得するセル範囲が大きくなったりします。
例えば、こんな表です。
7行目の高さが変更されています。
この状態で、先ほどと同じVBAコードを実行してみます。
行の高さを変えた行まで、選択してしまいました。
取得したいセル範囲とは違っちゃいますね。
最終手段としては、Endで最終行を取得して、表のセル範囲を取得するというものがあります。
Endで最終行を取得して、表のセル範囲を取得してみます。
最終行は、『.Cells(Rows.Count, "A").End(xlUp).Row』で取得できます。
最終列は、わかっているものとしています。
先ほどと同じ表で試してみます。
7行目の高さも変えておきました。
『End』を使って表のセル範囲を取得する、VBAコードは、次のようになります。
Sub TEST13()
Dim A
'最終行を取得
A = Cells(Rows.Count, "A").End(xlUp).Row
'表のセル範囲を選択
Range(Range("A1"), Cells(A, "C")).Select
End Sub
では、実行してみます。
表のセル範囲を、無事に取得することができました。
Endを使えば、空白行が表の中にあっても取得することができます。
ただ、VBAコードが難しくなるので、CurrentRegionで取得できなければ使う、という方がいいかと思います。
この記事では、表のセル範囲を簡単に取得することができる『CurrentRegion』についてご紹介しました。
『CurrentRegion』で表のセル範囲が取得できれば、1行目や最終列などの一部のデータを抜き出すことができます。
『CurrentRegion』の問題点は、空白行がある表です。
空白行を消したら話は早いですけども、実務では表を変更できない場合もありますよね。
空白行があるときには、『End』を使って最終行を取得して、表のセル範囲を取得する方法が確実です。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する