大体でIT

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

大体でIT

Excel VBAでUsedRangeで取得した範囲から、1行目や、1列目、最終行、最終列などを取得する方法についてご紹介します。UsedRangeを使えば、ワークシートで使用しているセル範囲を、簡単に取得できますので、便利です。

はじめに

この記事では、UsedRangeで取得したセル範囲から、1行目や、1列目、最終行、最終列などを取得する方法についてご紹介します。

UsedRangeは、ワーク―シートを指定するだけで、使用しているセル範囲を、簡単に取得できる便利なVBA関数です。

ただ、一つ弱点があって、書式の変更です。

UsedRangeは、書式を変更しただけでも、セルを使用していると認識して、大きなセル範囲を取得してしまいます。

書式の変更がある場合の、対処法として、CurrentRegionとEndを使った方法の2つを、併せてご紹介します。

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

この記事で紹介すること

  • UsedRangeの範囲からいろいろな値を取得
  • UsedRangeは書式変更が弱点

本記事の内容を動画でまとめています

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

目次

ポイントとなるVBAコード

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

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

With ActiveSheet.UsedRange
    .Select '使用しているセル範囲
    
    '行
    .Rows(1).Select '1行目
    .Rows(.Rows.Count).Select '最終行
    MsgBox .Rows(.Rows.Count).Row '最終行の行番号
    
    '列
    .Columns(1).Select '1列目
    .Columns(.Columns.Count).Select '最終列
    MsgBox .Columns(.Columns.Count).Column '最終列の列番号
End With

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

VBAで使用されているセル範囲を取得する

使用されているセル範囲を取得したい場合に、『UsedRange』はかなり使えます。

では、UsedRangeを使って実際に、使用されているセル範囲を取得してみます。

UsedRangeを使う

UsedRangeは、次のように入力します。

『ActiveSheet.UsedRange』

Rangeでセル範囲を指定する必要もなく、ワークシートだけを指定すると、使用しているセル範囲を取得することができます。

では、UsedRangeを使って、使用しているセル範囲を取得してみます。

次のような表を用意してみました。

表を用意

表を用意した

この表があるワークシートで、『UsedRange』を使ってみます。

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

実行してみます。

使用しているセル範囲を取得

UsedRangeを使った結果

値が入力されているセル範囲を取得することができました。

かなり簡単に、使用しているセル範囲を取得することができます。

次は、取得したセル範囲から、一部のデータを取得してみます。

1行目を選択する

UsedRangeで取得したセル範囲から、1行目を選択してみます。

行の取得には、『Rows』を使います。

Sub TEST2()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '1行目を選択
        .Rows(1).Select
    End With
    
End Sub

実行してみます。

1行目を選択

UsedRangeで取得したセル範囲から、1行目を選択した結果

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

最終行を選択する

UsedRangeで取得したセル範囲から、最終行を選択してみます。

同じように、行の取得には、『Rows』を使います。

最終行は、『セル範囲.Rows.Count』で取得することができます。

Sub TEST3()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '最終行を選択
        .Rows(.Rows.Count).Select
    End With
    
End Sub

実行してみます。

最終行を選択

UsedRangeで取得したセル範囲から、最終行を選択した結果

最終行だけを取得できました。

最終行の番号を取得

UsedRangeで取得したセル範囲から、最終行の行番号を取得してみます。

行の取得は、『Rows』でできます

最終行は、『セル範囲.Rows.Count』で取得します。

行番号を取得するには、『Row』を使います。

Sub TEST4()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '最終行の行番号を取得
        MsgBox .Rows(.Rows.Count).Row
    End With
    
End Sub

実行してみます。

最終行の行番号を取得

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

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

1列目を選択する

UsedRangeで取得したセル範囲から、1列目を選択してみます。

列の取得には、『Columns』を使います。

Sub TEST5()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '1列目を選択
        .Columns(1).Select
    End With
    
End Sub

実行してみます。

1列目を選択

UsedRangeで取得したセル範囲から、1列目を選択した結果

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

最終列を選択する

UsedRangeで取得したセル範囲から、最終列を選択してみます。

同じように、列の取得には、『Columns』を使います。

最終列は、『セル範囲.Columns.Count』で取得することができます。

Sub TEST6()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '最終列を選択
        .Columns(.Columns.Count).Select
    End With
    
End Sub

実行してみます。

最終列を選択

UsedRangeで取得したセル範囲から、最終列を選択した結果

最終列だけを取得できました。

最終列の番号を取得

UsedRangeで取得したセル範囲から、最終列の列番号を取得してみます。

列の取得は、『Columns』でできます

最終列は、『セル範囲.Columns.Count』で取得します。

列番号を取得するには、『Column』を使います。

Sub TEST7()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '最終列の列番号を取得
        MsgBox .Columns(.Columns.Count).Column
    End With
    
End Sub

実行してみます。

最終列の列番号を取得

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

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

表の値だけを取得

表のデータから、値だけを取得する方法です。

見出しはいらなくて、表のデータだけを取得したい場合に使えます。

表のデータから、値だけを選択するVBAコードです。

Sub TEST8()
    
    '使用しているセル範囲
    With ActiveSheet.UsedRange
        '表の値だけを選択
        .Rows(2 & ":" & .Rows.Count).Select
    End With
    
End Sub

実行してみます。

表のデータだけを選択

表のデータから値だけを選択した結果

表の値だけを、選択できました。

こんな感じで、UsedRangeを使えば、使用されているセル範囲を簡単に取得できて、そのセル範囲から一部のデータを取り出すこともできます。

UsedRangeは書式の変更でも認識してしまう

UsedRangeは、かなり便利ですけれども、注意点があります。

行の高さ変更や、罫線の追加などの、書式変更したセルについても、使用しているセルと認識してしまいます。

ちょっとやってみます。

書式を変更してみる

セルの書式を変更した、ワークシートを用意してみました。

セルの書式を変更してみた

セルの書式を変更してみた表

セルの罫線だけ追加してみました。

値は、入力していません。

では、罫線を追加したワークシートに『UsedRange』を使ってみます。

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

実行してみます。

書式を変更したシートで実行

表以外に罫線を追加したワークシートで、UsedRangeを使った結果

罫線を追加しただけのセルも、選択されました。

これが、UsedRangeの弱点です。

あと、セルの高さを変えても、使用している範囲と認識されます。

CurrentRegionを使ってみる

書式変更がある場合は、CurrentRegionが使えます。

先ほどの、書式変更したセルがあるワークシートでセル範囲を取得してみます。

書式変更されているシート

表以外で、書式変更されているシート

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

Sub TEST10()
    
    '現在のセル範囲を選択
    Range("B2").CurrentRegion.Select
    
End Sub

「.CurrentRegion」の使い方について、詳細はこちらでまとめています。

実行してみます。

表のセル範囲だけを取得

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

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

めでたし。としたいところですけども、CurrentRegionの弱点は、『空白行』です。

例えば、次のような表の場合です。

空白行がある表

空白行がある表

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

空白行の後が取得できない

空白行がある表で、CurrentRegionを使った場合

空白の行以降が、選択できていません。

こんな感じで、CurrentRegionにも弱点があります。

最終手段としては、『End』を使う方法があります。

書式変更や空白がある(Endをつかう)

Endを使えば、書式変更や空白行があっても、表のセル範囲を取得することができます。

先ほどの表を使います。

使う表

使う表

書式変更して、空白行もあります。

では、やってみます。

Endを使って表のセル範囲を取得するVBAコードです。

Sub TEST11()
    
    Dim A
    '最終行を取得
    A = Cells(Rows.Count, "B").End(xlUp).Row
    '表のセル範囲を選択
    Range(Range("B2"), Cells(A, "D")).Select
    
End Sub

列の範囲はわかっているものとしています。

実行してみます。

表全体のセル範囲を取得

Endを使って最終行を取得して、表全体のセル範囲を取得した結果

表全体のセル範囲を、選択することができました。

書式変更もして、空白行もあるけれども、正確に表全体を選択できています。

UsedRangeがダメで、CurrentRegionも失敗した場合は、Endを使って表全体を取得するといいです。

おわりに

この記事では、使用されているセル範囲を取得する、UsedRangeについて、ご紹介しました。

UsedRangeは、ワークシートの指定だけで、使用されているセル範囲を取得することができて、便利です。

ただ、行の高さ変更や、罫線の追加などの書式変更も、使用セルと認識してしまう点が弱点です。

代替案としては、CurrentRegionを使う方法と、Endを使って最終行を取得する方法があります。

罫線などがある場合は、CurrentRegionを使って、空白行もある場合は、最終手段として、Endを使って表全体のセル範囲を取得しましょう。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す