大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで表全体のセル範囲を取得する方法について、ご紹介します。表のセル範囲を取得するには、CurrentRegionがかなり便利です。ただ、空白行が弱点ですので、代替案として、Endを使って、表のセル範囲を取得する方法についてご紹介します。

はじめに

この記事では、表全体のセル範囲を取得する方法についてご紹介します。

表全体のセル範囲を取得するには、『CurrentRegion』がかなり便利です。

表全体のセル範囲を取得できれば、1行目だけとか、最終列だけとかを取得することができます。

ただ、CurrentRegionには、空白行という弱点がありますので、その場合の代替案についてもご紹介します。

では、表全体のセル範囲を、取得する方法について解説してきます。

この記事で紹介すること

  • 表全体のセル範囲を取得する方法

目次から見たい項目へ移動すると便利ですよ。

目次

ポイントとなるVBAコード

初めに、ポイントとなるVBAコードを記載しておきます。

VBAコードだけ確認したい場合に、ご活用ください。

'CurrentRegionでセル範囲を取得
With ActiveSheet.Range("A").CurrentRegion
    .Select '表の範囲を選択
    .Rows(1).Select '表の1行目を選択
    .Column(1).Select '表の1列目を選択
    .Rows(.Rows.Count).Select '表の最終行を選択
    .Column(.Columns.Count).Select '表の最終列を選択
    .Rows(2 & ":" & .Rows.Count).Select '表の値のみを選択
    b = .Rows(.Rows.Count).Row '表の最終行の行番号を取得
    b = .Columns(.Columns.Count).Column '表の最終列の列番号を取得
End With

'空白行がある場合の代替案(UsedRangeを使う)
ActiveSheet.UsedRange.Select

'空白行がある場合の代替案(Endを使う)
With ActiveSheet
    '最終行を取得
    a = .Cells(Rows.Count, "A").End(xlUp).Row
    '表のセル範囲を取得
    .Range(.Range("A1"), .Cells(a, "C")).Select
End With

では、解説していきます。

VBAで表全体の範囲を取得する

VBAで表全体のセル範囲を、簡単に取得する方法で、『CurrentRegion』を使います。

CurrentRegionは、セル範囲を取得する方法として、かなり使いますので、覚えておいて損はないです。

では、CurrentRegionの使い方を、具体的なVBAコードを使いながら、解説していきます。

CurrentRegionを使う

まず、CurrentRegionの使い方です。次のように入力します。

『Range("A1").CurrentRegion』

Rangeには、セルに入力されている表の左上のセルを指定します。

これだけで、表全体のセル範囲を取得できます。

次のような表で試してみます。

作ってみた表

作ってみた表

表全体のセル範囲を取得してみます。

Sub TEST1()
    
    '表のセル範囲を選択
    ActiveSheet.Range("A1").CurrentRegion.Select
    
End Sub

実行してみます。

表のセル範囲を取得

CurrentRegionで表のセル範囲を取得した結果

表全体が、選択されました。

表の位置が変わったとき

表の位置が変わったときについて、CurrentRegionを使ってみます。

表の位置を変えてみました。

表の位置を変えてみた

表の位置を変えてみた

この表のセル範囲を全て取得してみます。

やり方は、Rangeで指定するセルを変えるだけです。

Sub TEST2()
    
    '表のセル範囲を選択
    ActiveSheet.Range("B2").CurrentRegion.Select
    
End Sub

表の始まりが、『B2』から始まっているので、『Range("B2")』としています。

実行してみます。

表のセル範囲を取得

CurrentRegionで表のセル範囲を取得した結果

表全体のセル範囲を取得できました。

表の大きさを変えてみる

表の大きさを変えても、同じVBAコードで表全体を取得できます。

表の大きさを変えてみました。

表を大きくしてみた

表の大きさを大きくしてみた

この表のセル範囲を、CurrentRegionで取得してみます。

VBAコード自体は、先ほどと同じです。

Sub TEST3()
    
    '表のセル範囲を選択
    ActiveSheet.Range("A1").CurrentRegion.Select
    
End Sub

実行してみます。

表のセル範囲を取得

表全体のセル範囲をCurrentRegionで取得した結果

表全体のセル範囲を取得できました。

VBAコード自体は、同じです。

『CurrentRegion』、かなり便利です。

セル範囲の一部を取得

つづいて、CurrentRegionで取得したセル範囲から、一部のデータを取得する、というのをやってみます。

表全体のセル範囲ではなく、表の一部を取得したい場合もありますので、参考になるかと思います。

表の1行目を取得

CurrentRegionで取得したセル範囲から、表の1行目を取得したい場合です。

VBA関数の『Rows』を使います。

次のようなVBAコードで取得できます。

Sub TEST4()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の1行目を選択
        .Rows(1).Select
    End With
    
End Sub

実行してみます。

1行目を取得

CurrentRegionで取得したセル範囲から1行目を取得した結果

表の1行目だけを取得できました。

表の1列目を取得 

CurrentRegionで取得したセル範囲から、表の1列目を取得したい場合です。

VBA関数の『Columns』を使います。

次のようなVBAコードで取得できます。

Sub TEST5()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の1列目を選択
        .Columns(1).Select
    End With
    
End Sub

実行してみます。

1列目を取得

CurrentRegionで取得したセル範囲から1列目を取得した結果

表の1列目だけを取得できました。

表の最終行を取得

CurrentRegionで取得したセル範囲から、表の最終行を取得したい場合です。

VBA関数の『Rows』を使います。

最終行は、『オブジェクト.Rows.Count』で取得できます。

次のようなVBAコードで取得できます。

Sub TEST6()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の最終行を選択
        .Rows(.Rows.Count).Select
    End With
    
End Sub

実行してみます。

最終行を取得

CurrentRegionで取得したセル範囲から最終行を取得した結果

表の最終行を取得できました。

表の最終列を取得

CurrentRegionで取得したセル範囲から、表の最終列を取得したい場合です。

VBA関数の『Columns』を使います。

最終列は、『オブジェクト.Columns.Count』で取得できます。

次のようなVBAコードで取得できます。

Sub TEST7()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の最終列を選択
        .Columns(.Columns.Count).Select
    End With
    
End Sub

実行してみます。

最終列を取得

CurrentRegionで取得したセル範囲から最終列を取得した結果

表の最終列を取得できました。

表の値だけを取得

CurrentRegionで取得したセル範囲から、表の値だけを取得したい場合です。

VBA関数の『Rows』を使います。

最終行は、『オブジェクト.Rows.Count』で取得できます。

次のようなVBAコードで取得できます。

Sub TEST8()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の値だけを選択
        .Rows(2 & ":" & .Rows.Count).Select
    End With
    
End Sub

実行してみます。

表の値だけを取得

CurrentRegionで取得したセル範囲から値だけを取得した結果

表のデータから値だけを取得できました。

最終行の番号を取得

CurrentRegionで取得したセル範囲から、最終行の行番号を取得したい場合です。

VBA関数の『Rows』を使います。

最終列は、『オブジェクト.Columns.Count』で取得できます。

最後に、行番号を『Row』で取得します。

次のようなVBAコードで取得できます。

Sub TEST9()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の最終行の行番号を取得
        b = .Rows(.Rows.Count).Row
    End With
    
    MsgBox b
    
End Sub

実行してみます。

最終行の行番号を取得

CurrentRegionで取得したセル範囲から最終行の行番号を取得した結果

最終行の行番号を取得できました。

最終列の番号を取得

CurrentRegionで取得したセル範囲から、最終列の列番号を取得したい場合です。

VBA関数の『Columns』を使います。

最終列は、『オブジェクト.Columns.Count』で取得できます。

最後に、行番号を『Column』で取得します。

次のようなVBAコードで取得できます。

Sub TEST10()
    
    With ActiveSheet.Range("A1").CurrentRegion
        '表の最終列の列番号を取得
        b = .Columns(.Columns.Count).Column
    End With
    
    MsgBox b
    
End Sub

実行してみます。

最終列の列番号を取得

CurrentRegionで取得したセル範囲から最終列の列番号を取得した結果

最終行の列番号を取得できました。

セル範囲から取得するデータは、こんな感じでいろいろ取得することができます。

空白の行がある場合にCurrentRegionは注意

表のデータに空白の行があると、CurrentRegionでセル範囲を取得できません。

これは、ちょっと注意が必要です。

CurrentRegionで表の範囲を取得

では、表のデータに空白の行がある場合で、CurrentRegionを使って表のセル範囲を取得してみます。

使う表はこんな感じです。

空白行がある表

表のデータに空白行があるデータ

空白行がありますね。

では、CurretRegionでセル範囲を取得してみます。

Sub TEST11()
    
    '表のセル範囲を選択
    ActiveSheet.Range("A1").CurrentRegion.Select
    
End Sub

実行してみます。

セル範囲を取得(失敗)

CurrentRegionで空白の行がある表からセル範囲を取得した結果

空白の行以降のセル範囲が取得できていません。

でもまぁ、表なので空白行があるというのは、あまりないかと思います。

ただ、知っておけば、CurrentRegionでおかしな挙動をした時に、対策を立てやすくなります。

UsedRangeを使ってみる

空白行がある場合の対策としては、『UsedRange』を使う方法です。

では、空白行がある表で『UsedRange』を使ってセル範囲を取得してみます。

Sub TEST12()
    
    '使用しているセル範囲を選択
    ActiveSheet.UsedRange.Select
    
End Sub

実行してみます。

表のセル範囲を取得(成功)

UsedRangeを使ってセル範囲を取得した結果

表のセル範囲を取得できました。

ただし、UsedRangeは問題点があります。

UsedRangeは行の高さを変えたり、罫線があったとしても、その範囲を認識してしまって、取得するセル範囲が大きくなったりします。

例えば、こんな表です。

行の高さを変えた表

行の高さを変えた表

7行目の高さが変更されています。

この状態で、先ほどと同じVBAコードを実行してみます。

表のセル範囲を取得(失敗)

UsedRangeを使ってセル範囲を取得した結果

行の高さを変えた行まで、選択してしまいました。

取得したいセル範囲とは違っちゃいますね。

最終手段としては、Endで最終行を取得して、表のセル範囲を取得するというものがあります。

最終手段はEndで最終行を取得する

Endで最終行を取得して、表のセル範囲を取得してみます。

最終行は、『.Cells(Rows.Count, "A").End(xlUp).Row』で取得できます。

最終列は、わかっているものとしています。

先ほどと同じ表で試してみます。

空白行がある表

空白行がある表

7行目の高さも変えておきました。

『End』を使って表のセル範囲を取得する、VBAコードは、次のようになります。

Sub TEST13()
    
    With ActiveSheet
        '最終行を取得
        a = .Cells(Rows.Count, "A").End(xlUp).Row
        '表のセル範囲を選択
        .Range(.Range("A1"), .Cells(a, "C")).Select
    End With
    
End Sub

では、実行してみます。

表のセル範囲を取得

Endを使って表のセル範囲を取得した結果

表のセル範囲を、無事に取得することができました。

Endを使えば、空白行が表の中にあっても取得することができます。

ただ、VBAコードが難しくなるので、CurrentRegionで取得できなければ使う、という方がいいかと思います。

おわりに

この記事では、表のセル範囲を簡単に取得することができる『CurrentRegion』についてご紹介しました。

『CurrentRegion』で表のセル範囲が取得できれば、1行目や最終列などの一部のデータを抜き出すことができます。

『CurrentRegion』の問題点は、空白行がある表です。

空白行を消したら話は早いですけども、実務では表を変更できない場合もありますよね。

空白行があるときには、『End』を使って最終行を取得して、表のセル範囲を取得する方法が確実です。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す