Excel VBA(マクロ) そのK COPYの範囲、VLOOKUPの参照範囲にデータが追加された場合自動追加する(可変するテーブルを作成する)。ENDプロパティ、CurrrentRegionプロパティで実現。

Excel VBA(マクロ)でCOPYやVLOOKUPを使用すると、範囲指定をするため、最後の行のデータを追加するたびに範囲指定を変えなければ為らない。

毎回レンジとして(1:65536)を使用すると、COPYは1:65536の範囲をコピーするため膨大なデータ量になり、VLOOKUPも検索時間がかかる。

そこで、データが追加されても自動で範囲指定を更新するをENDプロパティ、CurrentRegionプロパティ使用する。

Excel関数ではOFFSETでデータ範囲を取得した。

さて、Excel VBA(マクロ)ではどうか。

データがある分だけ取得するマクロがある。

EndプロパティとCurrentRegionRegionプロパティである。

ただし、空白行があると、うまく行かない場合がある。


・ENDプロパティ
値があれば以下のように移動して行き、値がある最終セルの行番号、列番号(整数)を得る。
 xlToLeft 左方向への移動
 xlToRight 右方法への移動
 xlUp    上方向への移動
 xlDown   下方向への移動


Dim myRow As Integer , myCol As Integer
Dim myRng
myRow=Range("B3").End(xlDown).Row
myCol=Range("B3").End(xlToRight).Column
myRng= Range(Range("B3"),Cells(myRow,myCol)).Address

myRngには$B$3:$F$9等、データがある範囲のレンジを返してくれる。
空白行があると、そこで止まってしまうので、連続したデータがある行で指定する。

Workbooks(dejital).Worksheets(dejital1).Range("1:65536").Copy Workbooks(myname).Worksheets("dejital").Range("A1")

Workbooks(dejital).Worksheets(dejital1).Range("Rng").Copy Workbooks(myname).Worksheets("dejital").Range("A1")

コピーする範囲が少なくなり、Excelデータ量が減る。

myRowとか設定が面倒な場合は直接入力する。

myRng= Range(Range("B3"),Cells(Range("B3").End(xlDown).Row,Range("B3").End(xlToRight).Column).Address

Rngは再利用するため、関数として設定しておいた。


・CurrentRegionプロパティ
Endプロパティより簡単。

Dim myRng
myRng=Range("B3").CurrentRngion.Address

以上で終わり。

myRngには$B$3:$F$9等、データがある範囲のレンジを返してくれる。

多分、B3を基点として、B列、3行目でデータのある範囲を返してしていそうな感じである。

Workbooks(dejital).Worksheets(dejital1).Range("1:65536").Copy Workbooks(myname).Worksheets("デジタル").Range("A1")

Workbooks(dejital).Worksheets(dejital1).Range("Rng").Copy Workbooks(myname).Worksheets("デジタル").Range("A1")

CurrentRngionの方が簡単であるが、連続するデータの行、列を指定できない弱みがある。

使い分けよう。
忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP