エクセル VBA：複数のシートを1つに集約

質問

以前どこからか以下のようなVBAを見つけ使用していました。
今になり実情に合ったものに改良したいと思い始めたのですが、コピーをとる時のプロパティ UsedRangeが理解できません。
実はデータは少し不完全な場合があり、A列が他の列に比べ不足しております。
解説書などではUsedRangeを使えば、データの一番外枠、つまり全てのデータを含むようにコピーされると理解したのですが、違うのでしょうか。

データはこんな感じです。
A、B
XXX、BBB
XXX、BBB
、BBB
よろしくお願いします。

Sub Sample()
Dim sWS As Worksheet 'データシート（コピー元）
Dim dWS As Worksheet '集約用シート（コピー先）
Set dWS = Worksheets("AllData")
'集約用シートの2行目以降を削除
dWS.UsedRange.Offset(1, 0).Clear

'各シートの2行目以降のデータを、集約用シートの末尾にコピー
For Each sWS In Worksheets
If sWS.Name <> dWS.Name Then
With sWS.UsedRange
'コピー元シートにデータが1件以上ある場合
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1).Copy _
Destination:=dWS.Cells(Rows.Count, 1). _
End(xlUp).Offset(1, 0)
End If
End With
End If
Next sWS
End Sub

回答No.4

chie65535 · ベストアンサー

追記。

UsedRangeには、データが入ってないが罫線だけは入っていたり、データが入っていないが塗り潰しされているなどのセルも範囲に含まれてしまう、という欠点があります。

なので「データは一切入ってないが、罫線が引いてある」とかだと、失敗します。

一方、
Destination:=dWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
は「罫線などを無視し、データが入っている行の、最後の行」を指定できますが「データが足りない列では失敗する」という欠点があります。

なので「それぞれの欠点に合わせて、どちらか一方の処理をうまく選ぶ」必要があります。

例えば「B列は、必ずすべての行が埋まっている状態にして、B列を基準に、最後の行を求める」とか「罫線や塗り潰しを入れてないシートではUsedRangeを使う」とか、欠点が結果に影響しない方法を選ぶ必要があります。

お礼

コメントありがとうございます。
まずVBAの中身を知りたかったというのもあり、質問させていただきましたが、ご指摘のとおりこのVBAが機能するようにデータ管理をしていきたいと思います。

回答No.5

imogasi

UsedRangeに関するメモーー＞注意・参考事項
・シートを指定する
・飛び離れたセルのデータも含めて、四角（長方形）範囲をつかむ
・問題は、目に見えない、「主データ範囲」と飛び離れたブランク１・数文字のセルで、これも含まれる。誤って入ってしまった空白セル、ごみデータセルも含んだ領域をつかむ
・関数で＝IF(A2=1,"1","")のような式を入れているが、空白該当の場合で、見た目空白のセルでも含んだ範囲をつかむ
・「主データ範囲」と飛び離れたセルにデータを入れて、その後Deleteすると、そのセルが含まれない。
以上を参考にしてください。
ーー
あとCurrentRegionがあるから、WEBででも調べて、使えないか勉強のこと。
Worksheets("SheetX").Range("A50000").End(xlUp)・・方式のメリットも検討してみては。
ーー
小生がテストしてみたコード例（質問のケースでは全然ない）
Sub test01()
Worksheets("Sheet1").UsedRange.Select
MsgBox Worksheets("Sheet1").UsedRange.Rows.Count
MsgBox Worksheets("Sheet1").UsedRange.Columns.Count
End Sub

Sub test02()
'第１行目、第二行目空白行
Worksheets("Sheet1").Range("A3").CurrentRegion.Select
MsgBox Selection.Rows.Count
MsgBox Selection.Columns.Count
Selection.End(xlToRight).Columns.Select
'MsgBox Selection.End.Rows.Count
End Sub
ーー
参考サイト
'http://excel-ubara.com/excelvba4/EXCEL222.html
'http://www.officepro.jp/excelvba/cell_range/index5.html
ーー
質問のケースが具体的にどういうものかしつもんぶんしょうからは伝わらない
＞データはこんな感じです。
よくわからない。
＞コード例
うまく行かないコード例など挙げても無駄。
実行した場合、どういう不都合が起こっているか文章で、説明したほうがよい。
文章で説明できるようになってはじめて、事態が「わかった」レベルだと思うから。
ーー
１シートの集約したいなら、十分下の方のセルから
End(xlup)で前回集約後のデータの最下行をつかみ、１行下からに張り付ければしまい。
例
Sheet1は毎回集約していく、集約シートとする。
Sub test03()
Worksheets("Sheet1").Range("A50000").End(xlUp).Select
Worksheets("Sheet1").Range("A50000").End(xlUp).Offset(1, 0).Select　＜－－直下行
End Sub
集約する各シートの見出し行が最上行にあるときや各シートで見出し部分が異なるときは、それに対して、省く工夫（コード追加）がいる。
対象外のシート（集約結果シートを含めて）がある場合は、集約処理をスキップするコードが必要。

回答No.3

kagakusuki

御質問文にあるVBAは

>以前どこからか以下のようなVBAを見つけ使用していました。

という事であって、それのどういった点が実情に合っていないのかという事が何も説明されていないため、具体的なVBAの例を提示する事は出来ませんが、少なくとも

>UsedRangeを使えば、データの一番外枠、つまり全てのデータを含むようにコピーされると理解したのですが、違うのでしょうか。

という考え方をしておられるのであれば、それは少し間違っています。
　UsedRangeは、その名の通り「使用しているセル範囲」の事であり、使用しているという事にはデーターが存在しているという事だけではなく、表示形式やフォントの設定、罫線、塗りつぶし色、条件付き書式、それにコメントなども含まれるのですから、例えデータが存在していなくとも、何らかの書式やコメントが設定されているセルがあれば、それはUsedRangeに含まれます。
　又、UsedRangeは「使用しているセル範囲」の事なのですから、例えばA列や1行目の中に使用中のセルが存在していない場合には、A1セルはUsedRangeに含まれない事になります。
　例えば、E3:G5のセル範囲にのみデータが入力されていて、J3セルにはコメントが付けられていて、C16セルに罫線が設定されていて、その他のセルは何も使用されていない、という場合には、UsedRangeはC3:J16のセル範囲となり、1行目～2行目やA列～B列のセルはUsedRangeには含まれません。

回答No.2

f272

UsedRangeというのはセルA1からシートの最後のセルまでの範囲です。
シートの最後のセルは，そのシートの中でctrl-Endキーを押せばそこにジャンプするので確認できます。

回答No.1

chie65535

＞dWS.UsedRange.Offset(1, 0).Clear

集約用シートの「最初の見出し」以外をクリアしています。

見出しが「４行目」にあって（１～３行目は何も入力されてない空欄）、データが「５行目から１０行目」まで入っている場合

UsedRange.Offset(1, 0)

は「データが入っている範囲を、１行下にズラした範囲」を意味するので、上記のケースでは「５行目から１１行目」がクリアされます。

つまり「見出し以外をクリア」します（見出しは何行目にあっても構いません）

＞For Each sWS In Worksheets

sWSは「すべてのシート」をループします。

＞If sWS.Name <> dWS.Name Then

sWSで示すシートが「集計用シートじゃない場合」だけ実行します。

＞With sWS.UsedRange

ここから、コピー元シートの、データが入っている範囲について処理します。

＞If .Rows.Count > 1 Then

「行数が２以上」つまり「見出し以外に、データが１件でもあれば」以下を処理します。

「見出しだけ」だったり「何も入力されていないシート」の場合、行数（Rows.Count）は「１以下」になるので、除外します。

＞.Offset(1, 0).Resize(.Rows.Count - 1).Copy Destination:=dWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

「.Offset(1, 0)」で「コピー元のデータが入っている範囲を１行下にズラした範囲」になります。つまり「見出しの行の次の行から、データが入っている末尾の次の行まで」です。この範囲は「１行ズラしただけ」なので「末尾に１行分、余計な行がある」と言う事に注意して下さい。

「Resize(.Rows.Count - 1)」で「１行分、余計な分を削って」います。

つまり「.Offset(1, 0).Resize(.Rows.Count - 1)」で「見出しを除いた、データだけの部分」になります。

そして、その範囲を「Copy」メソッドでコピーしています。

また、コピー先として「dWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)」を指定しています。

「Rows.Count」は（頭にピリオドが無い事に注意）「シートの最大行数」を返します（Excel2000などでは65536になります）

「Cells(Rows.Count, 1)」で「A列の65536行」を意味します。

「Cells(Rows.Count, 1).End(xlUp)」で「A列でデータ入力されている行の末尾の行」になります。

「dWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)」で「A列でデータ入力されている行の末尾の行の、次の行」になります。

ここで、質問者さんのケースでは

＞A列が他の列に比べ不足しております。

という問題がある為「コピー先の指定が、間違った指定」になってしまいます。

「A列の末尾」では「A列のデータが不足している場合」に、間違ったコピー先になってしまいます。

従って「A列のデータが不足している場合」に対処するには

Destination:=dWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

を

Destination:=dWS.Cells(dWS.UsedRange.Row + dWS.UsedRange.Rows.Count, 1)

に変更すれば「コピー先の指定が、コピー先のシートの末尾の次の行」になります。

お礼

詳しい丁寧な説明大変ありがとうございます。データが不足している部分は消されずに残っています。
しかしDestination:=dWS.Cells(dWS.UsedRange.Row + dWS.UsedRange.Rows.Count, 1)だけを置き換えたところ、二つ目のシートのデータがコピーされなくなりました。

このステートメント？を詳しく教えていただけますか。