VBAで、行数が変化する表の合計を求めたい。No2

質問

質問番号：4708445で回答頂きありがとうございます。引き続き申し訳ありません。
質問内容が文字数制限を意識する余り、舌足らずとなっておりました。
　表構成(行見出し）は以下の通りです。
　　年度・お名前・ランク１・ランク２・金額　(ランクは１～４）　このような複数の表をランク１、ランク２でフィルタ抽出（ランク１＝１、ランク２＝１等）し、抽出結果を集計シートに転機します。転記する際にラスト行を取得して合計を求めるのですが、抽出ランクにより、表毎に抽出先の数は変化（よって抽出行は可変する）します。抽出条件を変えるため、結果、集計シートは、上記行見出しは同じですが、行数は異なる表が複数貼付られます。貼付時、第１の表から3行下空白で第２の表以下を貼付します。その後、表毎の最終行に合計（SUM）したいのですが、表現が稚拙で申し訳ありませんでした。
　SUBTOTAL関数ですと、第１の表の合計セルの式をコピペで行うようになると思いますが、ここ作業をＶＢＡではできないのでしょうか？

回答No.3

n-jun · ベストアンサー

＃２です。

貼付けた表がいくつもあり、表と表の間に空白行（２行以上とか）があって、
そのうちの上の空白行に合計を入れるとします。

例えばＡ列に数値が入力されてますが、ちらほら空白セルがあったとします。
その空白セルに上のセルの合計を代入するとしたら、

Sub try()
Dim r As Range

For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlTextValues).Areas
r.Offset(r.Rows.Count).Resize(1).Value = WorksheetFunction.Sum(r)
Next

End Sub

＞表毎の最終行に合計（SUM）したいのですが
こんな感じのものかな。

お礼

n-junさん、早速の回答、本当にありがとうございました。
ご教示頂いたコードにより、表毎に合計を求めることができました。
web2525さんも、SUBTOTAL関数の存在を教えて頂き、ありがとうございました。

回答No.2

n-jun

＞質問番号：4708445
は終りであれば閉じて下さい。

貼付けた後の事であれば、例えばＡ列の合計なら
Sub try()
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
.Offset(.Rows.Count).Resize(1).Value = WorksheetFunction.Sum(Range(.Address))
End With
End Sub

でＡ列の最終行の１つ下に合計を表示できます。

それ以外については表構成が不明なのでわかりません。
⇒表構成はシート上にどのように表が作られているか？
　Ａ列が年度とか。２行目に項目行があり３行目以下にデータを貼付けるとか。

補足

＞質問番号：4708445
は締切ました。
それと誠に申し訳ありません。表構成（行見出し）でなく、列見出しです。すみませんでした。
貼付後の表構成は、以下の通りです。

　　　年度　お名前　　ランク１　ランク２　金額
１　　　１８　あああ　　　１　　　　　２　　　１０
２　　　１８　いいい　　　１　　　　　２　　　２０
３　　　１８　おおお　　　１　　　　　２　　　１５
４
５
６
７　　年度　お名前　　ランク１　ランク２　金額
８　　　１８　えええ　　　２　　　　　１　　　１０
９　　　１８　かかか　　　２　　　　　１　　　１５
１０
１１
１２
１３　年度　お名前　　ランク１　　ランク２　金額
１４　　１８　ききき　　　　２　　　　　　１　　　１０
１５　　１８　さささ　　　　２　　　　　　１　　　　５
１６　　１８　せせせ　　　２　　　　　　１　　　２０
１７　　１８　けけけ　　　２　　　　　　１　　　　５
１８　　１８　こここ　　　　２　　　　　　１　　　１５
１９

この表の最終行の下（４行目）に金額合計を表示、次の表の最終行（１０行目）に金額合計を表示・・・としたいのです。表ごとに行数が変わるので合計行の取得に悩んでいます。
n-junさんのSub try()を上の表で、
With Range("E1", Cells(Rows.Count, 5).End(xlUp))
.Offset(.Rows.Count).Resize(1).Value = WorksheetFunction.Sum(Range(.Address))
として、実行したのですが、4行目に合計が表示されず、19行目に累計で表示されます。もともと、当方の質問が拙いためご迷惑をおかけしておりますが、引き続き、ご教示願います。

回答No.1

web2525

フィルターでの抽出は表示上の変更だけ
（条件に合わない行は表示していないだけ)

SUM関数だと表示されていないセルまで合計されます。

そのための関数としてSUBTOTAL関数が存在します。

マクロで最終行を求めた場合のセル行はフィルターに掛ける前の行番号と同じ行番号を示します。

フィルターを掛けた場合の合計値を求めるのであれば、データの最終行の下にSUBTOTALで合計を求める式を入れ、マクロ上でそのセル値を参照すれば良いだけです。

フィルターで表示行が変化しても集計セルの位置は変化しません。