ここでは For Each × Offset × Resize を組み合わせて「表全体を矩形単位で処理する」パターンをまとめます。
行ループ・列ループよりも一段階広く、ブロック（矩形範囲）単位での加工・集計・抽出を自在に扱える黄金テンプレです。
応用パターン 10選
1. 表をブロック単位で右側へコピー
Dim blk As Range
For Each blk In Range("A1:D20").Areas
blk.Resize(blk.Rows.Count, blk.Columns.Count).Offset(0, blk.Columns.Count + 1).Value = blk.Value
Next blk
👉 表全体をブロック単位で右側へ複製。
2. ブロックごとに小計を下段へ追加
Dim blk As Range
For Each blk In Range("A1:D5,A6:D10,A11:D15").Areas
blk.Offset(blk.Rows.Count, 0).Resize(1, blk.Columns.Count).Value = WorksheetFunction.Sum(blk)
Next blk
👉 複数ブロックごとに小計行を下段へ追加。
3. ブロックごとに平均値を右隣へ
Dim blk As Range
For Each blk In Range("B2:E6,B7:E11").Areas
blk.Offset(0, blk.Columns.Count).Resize(blk.Rows.Count, 1).Value = WorksheetFunction.Average(blk)
Next blk
👉 各ブロックの平均値を右隣列へ展開。
4. ブロックごとに最大値を右隣へ
Dim blk As Range
For Each blk In Range("C2:F6,C7:F11").Areas
blk.Offset(0, blk.Columns.Count).Resize(blk.Rows.Count, 1).Value = WorksheetFunction.Max(blk)
Next blk
👉 各ブロックの最大値を右隣列へ展開。
5. ブロックごとに最小値を右隣へ
Dim blk As Range
For Each blk In Range("C2:F6,C7:F11").Areas
blk.Offset(0, blk.Columns.Count).Resize(blk.Rows.Count, 1).Value = WorksheetFunction.Min(blk)
Next blk
👉 各ブロックの最小値を右隣列へ展開。
6. ブロックごとに件数を右隣へ
Dim blk As Range
For Each blk In Range("A2:D6,A7:D11").Areas
blk.Offset(0, blk.Columns.Count).Resize(blk.Rows.Count, 1).Value = WorksheetFunction.Count(blk)
Next blk
👉 各ブロックの件数を右隣列へ展開。
7. ブロックごとに異常値件数を右隣へ
Dim blk As Range, cell As Range, cnt As Long
For Each blk In Range("B2:E6,B7:E11").Areas
cnt = 0
For Each cell In blk.Cells
If IsNumeric(cell.Value) And cell.Value < 0 Then cnt = cnt + 1
Next cell
blk.Offset(0, blk.Columns.Count).Resize(blk.Rows.Count, 1).Value = cnt
Next blk
👉 各ブロックの負の値件数を右隣列へ展開。
8. ブロックごとに累積値を下方向へ
Dim blk As Range, sumVal As Double, r As Range
For Each blk In Range("C2:F6,C7:F11").Areas
sumVal = 0
For Each r In blk.Rows
sumVal = sumVal + WorksheetFunction.Sum(r)
r.Cells(1, blk.Columns.Count).Offset(0, 1).Value = sumVal
Next r
Next blk
👉 各ブロックの累積値を右隣列へ展開。
9. ブロックごとに差分を右隣へ
Dim blk As Range, r As Range
For Each blk In Range("A2:D6,A7:D11").Areas
For Each r In blk.Rows
If r.Row > blk.Row Then
r.Cells(1, blk.Columns.Count).Offset(0, 1).Value = WorksheetFunction.Sum(r) - WorksheetFunction.Sum(r.Offset(-1, 0).Resize(1, blk.Columns.Count))
End If
Next r
Next blk
👉 各ブロックの行ごとの差分を右隣列へ展開。
10. ブロックごとにタグを付与
Dim blk As Range
For Each blk In Range("A2:D6,A7:D11").Areas
blk.Offset(0, blk.Columns.Count).Resize(blk.Rows.Count, 1).Value = "Block_" & blk.Row
Next blk
👉 各ブロックの右隣列に「ブロックタグ」を付与。
✅ まとめ
- For Each blk In Range(…).Areas → ブロック単位でループ
- Offset → 隣列や下段に結果を出力
- Resize → ブロックサイズを拡張して一括処理
- 応用編 → 小計・平均・最大/最小・件数・異常値・累積・差分・タグ付与
💡 この「ブロックループ × Offset × Resize 応用編」をベースにすれば、表全体を 矩形単位で自動処理でき、売上表・勤怠表・在庫表などの業務別ライブラリに直結します。
