VBAのoffsetの動き方について教えてください

やりたいこと：地域ごとに作っているデータがありそのデータを一つのシートにまとめたい。
シート名＝地域　になっているのでまとめるシートにシート名も追加したい。
サンプルのVBA
Sub sh1()

Dim A
'2つ目のシートから最終シートまでループ
For i = 2 To Sheets.Count
'まとめシートの最終セルを取得
Set A = Sheets("統合").Cells(Rows.Count, "A").End(xlUp)
With Sheets(i).Range("A1").CurrentRegion
'データ部分をコピー
.Resize(.Rows.Count - 1).Offset(1, 0).Copy A.Offset(1, 1)
'シート名を入力
A.Offset(1, 0).Resize(.Rows.Count - 1) = Sheets(i).Name
End With
Next

End Sub

ー－－
ここで、わからないのが、素直にデータの開始がA1ならよいのですが、A3からタイトル行があり、データが入っています。
With Sheets(i).Range("A1").CurrentRegion
ここは、
With Sheets(i).Range("A3").CurrentRegion

になおす、そして、
'データ部分をコピー
.Resize(.Rows.Count - 1).Offset(1, 0).Copy A.Offset(1, 1)
ここのoffsetの値をどうすればいいのかがわかりません。
どなたかご教示ください。

No.1 ベストアンサー 回答者： Qchan1962 回答日時： 2022/11/26 00:28

こんばんは

この様な疑問が所維持た場合、1つずつ整理して確認するのが良いと思います
どのように確認するのか・・・
ご質問の場合は、実行によるセル範囲を確認すれば良いと思いますので
各セル範囲取得コードの命令をSelectとかDebug.Print 命令.Address
などとしてステップ実行などで確認していくと命令の挙動がつかめます

考えすぎかも知れませんが、ご質問の中で躓きになっている原因は
.CurrentRegionでは無いかと推測します

A1から始まるターゲット範囲があるとして、（１行目が見出し行）
これが上手う行くとして・・
これをA3～にすると　とありますが・・
1，２行目にデータが無い空行の場合にしか思うようにはなりません

言い方を変えると1，２行目にデータがある場合は
Sheets(i).Range("A1").CurrentRegion　も
Sheets(i).Range("A3").CurrentRegion　も同じ範囲がを示すと言う事です

従って、.Resize　.Offset 共に１行目A1が起点（最上部左セル）になるので3つ下にずらし3つ小さくする必要があると思われます
(3行目見出しの場合A4以下）
.Resize(.Rows.Count - 3).Offset(3, 0)

もし、１，２行目にデータが無ければ（3行目見出し行）
Sheets(i).Range("A3").CurrentRegionがデータ範囲のみを取得するので　.Resize　.Offset 共にそのまま（１つ下で１つ小さくする）で良いと思います


Sub sh1()

Dim A
'2つ目のシートから最終シートまでループ
For i = 2 To Sheets.Count
'まとめシートの最終セルを取得
Set A = Sheets("統合").Cells(Rows.Count, "A").End(xlUp)
Sheets(i).Select
Sheets(i).Range("A1").CurrentRegion.Select
With Sheets(i).Range("A1").CurrentRegion
'データ部分をコピー
.Resize(.Rows.Count - 1).Offset(1, 0).Select
Sheets("統合").Select
A.Offset(1, 1).Select
'シート名を入力
A.Offset(1, 0).Resize(.Rows.Count - 1).Select
End With
Next
End Sub

上記確認コードはコピペで作ったのでエラーがあったら許して
F8キーで（各定数を変えて）試してみてください
変化がわかれば簡単ですよね

この回答へのお礼

こんばんは
.CurrentRegionのこと
目からうろこでした。offsetにばかりとらわれていて、
.CurrentRegionは、なんかそのままかけばいいのかぐらいの浅い考えでした。まさにおっしゃる通りでした。
サンプルもありがとうございます。
定数をかえつつ頑張ってみます。
１こずつ考えていくことができていませんでした。思い込みが強かったです。

お礼日時：2022/11/27 01:32

No.3 回答者： めぐみん_ 回答日時： 2022/11/26 10:33

回答がダブってたらごめんなさい。

＞ここのoffsetの値をどうすればいいのかがわかりません。
項目行を抜いてデータだけをと言うのなら変える必要はないのでは？
質問文からは１行目・２行目には何も入力がないと読み取れます。

それとも何かタイトルの様なものが入力されているのでしょうか？

この回答へのお礼

こんばんは、ありがとうございます。
1行目、2行目はメモ的なものがはいっています。
今月の集計実施日、営業部門コードなどです。
上司に確認したら、そこはけさないでね。そのままで計算してといわれました。A1からにしたい

お礼日時：2022/11/27 01:25

No.2 回答者： fujillin 回答日時： 2022/11/26 00:50

こんばんは

CurrentRegionは指定したセルと連続してデータが入力されているセル範囲を取得します。
ですので、そのシートの1行目や2行目の状態によって、
　Sheets(i).Range("A3").CurrentRegion
で得られるセル範囲の左上は、A1セル、A2セル、A3セルの3通りの可能性が考えられます。

3行目を除いて、4行目以降を指定するのなら、範囲の左上が
　A1の場合、減ずる行数＝3、オフセット＝3行
　A2の場合、減ずる行数＝2、オフセット＝2行
　A3の場合、減ずる行数＝1、オフセット＝1行
とすれば良いことになります。
もしも、1行目、2行目の状態が定型で固定ならば、場合分けは不要で固定値でも処理できますね。
ちなみに、上記の差分の行数は
　4 - .Cells(1).Row
で計算することができます。

ご提示のサンプルでは、1行目がタイトルで、2行目以降を指定したいので、行数-1、オフセット+1と固定値にしていることになります。
いずれの場合でも、「タイトル行のみでデータがない場合」にはresize後の行数として「0」を指定することになってしまうので、エラーが発生します。
「必ずデータが存在する」保証があるなら、気にする必要はありませんけれど、そうでない場合は、「対象行数が0ではない」ことをチェックしておく方が良いかも知れません。


別の方法として、「最終行を求める」という方法もあります。
ご存じとは思いますが、
　lastR = Cells(Rows.Count, 1).End(xlUp).Row
でA列の最終行を求めることができますので、これを利用して
　・lastRが4以上であれば、4行目から「lastR - 3」行分を取得
　・lastRが3以下ならば、（データがないので）処理は飛ばしてもよい
とすることも可能です。

この回答へのお礼

ありがとうございます。
とまどいながらですが１こ１こ進めていきます。
最終行、そのほうがいいかもとあらためて思いました。

お礼日時：2022/12/03 11:04