大体でIT

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

大体でIT

Excel VBAでRangeやCellsのセル範囲を移動する方法についてご紹介します。使うVBA関数は、『Offset』です。Offsetを使えば、簡単に、表のデータから値のみを取得することができます。

はじめに

この記事では、RangeやCellsのセル範囲を移動する方法についてご紹介します。

セル範囲の移動は、Offsetを使えばできます。

Offsetの使いどころとしては、『表のデータから値のみを取得する』場合です。

表は基本的に、見出しがありますので、CurrentRegionで取得すると簡単ですけども、見出しがついてきちゃいます。

Offsetを使えば、見出しなしで、値のみを取得できますので、便利です。

では、セル範囲を移動する『Offset』について、解説してきます。

この記事で紹介すること

  • Offsetを使ってセル範囲を移動する
  • 表のデータから値のみを取得する

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

目次

ポイントとなるVBAコード

初めに、ポイントとなるVBAコードについて、ご紹介します。

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

ActiveSheet.Cells(1, 1).Offset(2, 3).Select 'Cellsを2行と3列だけ移動する
ActiveSheet.Range("A1").Offset(2, 3).Select 'Rangeをを2行と3列だけ移動する

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

VBAでRangeやCellsの範囲を移動する

RangeやCellsの範囲を移動するには、『Offset』を使います。

Offsetの使い方を、具体的なVBAコードを交えて、解説していきます。

Offsetの使い方

まず、Offsetは次のように入力して使います。

『Range("A1").Offset(行, 列)』

行や列には、移動させたい、行数と列数を入力します。

  • 行:移動させたい行数
  • 列:移動させたい列数

Offsetを使うイメージは、次のような感じです。

Offsetのイメージ

Offsetを使ったイメージ

セルA1から、Offsetで、2行3列だけ移動させています。

では、実際に『Offset』を使って、VBAコードを書いてみましょう。

Cellsのセル範囲を移動してみる

まずは、Cellsのセル範囲を移動してみます。

Cellsのセル範囲を移動

セルA1を、Offsetを使って、2行3列だけ移動して、セルを選択してみます。

『.Offset(2, 3)』というように、入力します。

Sub TEST1()
    
    'Cellsを2行と3列だけ移動
    ActiveSheet.Cells(1, 1).Offset(2, 3).Select
    
End Sub

実行してみます。

Cellsのセル範囲をOffsetを使って移動して、選択した結果

結果は、セルD3が選択されました。

セルA1を、2行と3列移動させると、セルD3となりますので、Offsetでうまく移動ができました。

というような感じで、Offsetが使えます。

Offsetには、マイナスの値も入力することができます。

やってみます。

Offsetにマイナスの値を入力してみる

セルD3を、Offsetを使って、2行戻して、3列戻してみます。

『.Offset(-2, -3)』というように、入力します。

Sub TEST2()
    
    'Cellsを2行と3列だけ戻る
    ActiveSheet.Cells(3, 4).Offset(-2, -3).Select
    
End Sub

実行してみます。

Offsetを使って、Cellsのセル範囲を戻した結果

結果は、セルA1が選択されました。

Offsetに、マイナスの値を入力すれば、セル範囲を戻すことができます。

Rangeのセル範囲を移動してみる

Rangeのセル範囲を、Offsetを使って移動してみます。

RangeでもOffsetは使えます。

どちらかというと、OffsetはRangeと一緒に使う方が多いです。

Range("A1:B2")のセル範囲を、2行と3列だけ移動して、選択してみます。

Sub TEST3()
    
    'Rangeを2行と3列だけ移動する
    ActiveSheet.Range("A1:B2").Offset(2, 3).Select
    
End Sub

実行してみます。

Rangeのセル範囲を移動

Rangeのセル範囲を、Offsetを使って移動した結果

結果は、Range("D3:E4")の範囲が選択されました。

Range("A1:B2")を2行と3列だけ移動すると、Range("D3:E4")となりますので、Offsetでうまく移動できています。

セル範囲を移動させるだけ、なんてあまり使わないと思います。

次に、Offsetを使った実用的な列をご紹介していきます。

VBAで表の値だけを取得する

Offsetは、表のデータを取得する際に、活躍してくれます。

表は、基本的に見出しがついています。

なので、CurrentRegionなどで表のデータを取得すると、見出しまで取得しちゃいます。

こんなときに、Offsetを使うと、表のデータから、値の部分だけを取得することができます。

ちょっとやってみます。

表のデータ

まず、表のデータをこんな感じで作ってみました。

作ってみた表のデータ

表のデータ

見出しがあって、値が適当に入力されています。

これから、値だけを取得してみます。

OffsetとResizeを使って取得

表のデータから、値だけを取得してみます。

取得だとわかりづらいので、まずは、セル範囲を選択からやってみます。

値のみを選択

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

Sub TEST4()
    
    '表の範囲を設定
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    '表の値だけを選択
    a.Resize(a.Rows.Count - 1).Offset(1, 0).Select
    
End Sub

実行してみます。

OffsetとResizeで表の値だけを選択した結果

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

選択できるということは、表の値だけのセル範囲を取得できているので、値を取得することも、もちろんできます。

ここで、新しく『CurrentRegion』と『Resize』というVBA関数がでてきましたので、簡単に紹介しておきます。

CurrentRegionとは

CurrentRegionは、同じ表と認識できるセル範囲を、一発で取得してくれるVBA関数です。

CurrentRegionだけを使ってみます。

Sub TEST5()
    
    '表の範囲を設定
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    '表を選択
    a.Select
    
End Sub

実行してみます。

CurrentRegionで表を全て選択

CurrentRegionを使えば、表を全て選択することができます。便利な関数です。

Resizeとは

セル範囲の大きさを、変更するVBA関数となります。

例えば、

  • .Resize(2, 3):セル範囲を、2行3列にする
  • .Resize(1):セル範囲を、1行にする
  • .Resize(,2):セル範囲を、2列にする

といった感じです。

CurrentRegionとResizeだけで表を選択してみます。

Sub TEST6()
    
    '表の範囲を設定
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    'セル範囲を1行減らす
    a.Resize(a.Rows.Count - 1).Select
    
End Sub

実行してみます。

CurrentRegionとResizeを使った場合

行の大きさを、1行だけ減らせました。

あとは、『Offset』で1行だけ下に移動すると、表の値だけを選択することができます。

では、表の値のみを選択できましたので、次は、値のみを取得してみます。

表のデータから値のみを取得

VBAコードは、先ほどの選択とほぼ同じです。

Sub TEST7()
    
    '表の範囲を設定
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    '表の値だけを取得
    b = a.Resize(a.Rows.Count - 1).Offset(1, 0)
    
End Sub

実行して、表のデータから値のみを取得してみます。

Offsetを使って、表のデータから値のみを取得した結果

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

配列になっているので、ちょっと見ずらいですけども、ちゃんと値のみ取得できています。

こんな感じで、Offsetは、表のデータから値を取得するときに、活躍します。

おわりに

この記事では、セル範囲を移動させる『Offset』について、解説しました。

Offsetは、CellsでもRangeでも使えます。

どちらかというと、Rangeと組み合わせて、値を取得するのに使われるのが多いです。

Offsetを使うメリットは、表のデータから値のみを取得したい、という場合に便利なことですね。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す