範囲内に合計を出す

質問

毎日SUM関数で合計を出していますが、もっと簡単に出せないかと思い質問しました。H7からデーター(数字)が下に入っていきます。(H7.H8.H9.・・・・・)日によ
って最後の行が違います。(シートは毎日違いますが最初に入る場所はH7からと決まっています。)昨日はH7～H20でした。今日はH7～H27ですと日によってばらばら
です。計算のしかたですが、H20で終わった時は、合計ボタンを押したらH7～H20までをH1に合計を表示してH列の最終行の次も合計を表示する所まではなんとか
出来ましたが、L列から以降のセルにデーターがあっても最終行と判断しない様にするにはどうすればいいですか。（A列～K列で最終行がK25の時M列のM30にデーターがある時はH31が最終行の次と表示しますが本当はH26と表示してほしいです）アプリケーションはExcel2000です。よろしくお願いします。
Private Sub CommandButton1_Click()
Dim maxRow As Long
maxRow = Application.Max(Range("H65536").End(xlUp).Row, 5)
Range("H1") = Application.Sum(Range("H7:H" & maxRow))

Range("A1:K2").Copy
With ActiveSheet.UsedRange
.Cells(.Count + 1).Select
End With
ActiveSheet.Paste

Application.CutCopyMode = False
With ActiveSheet.UsedRange
.Cells(.Count + 1).Select
End With
End Sub

回答No.4

nishi6 · ベストアンサー

データ単位はいつも２行として、２行単位でコピーしています。
最終行を求めたら、それは２行を１行にした上の行なので、貼り付け先の行は＋２すればいい事になります。

Ｌ列には余分な操作はしません。
コピーしているのはA～K列です。あっていればいいんですが・・・・・

Private Sub CommandButton1_Click()
　　Dim maxRow As Long
　　Dim cRow As Long
　　Dim col As Integer
　　maxRow = 7
　　For col = 1 To 11
　　　　cRow = Cells(65536, col).End(xlUp).Row
　　　　If maxRow < cRow Then maxRow = cRow
　　Next

　　Range("H1") = Application.Sum(Range("H7:H" & maxRow))
　　Range("A1:K2").Copy
　　Cells(maxRow + 2, 1).Select '2行単位なので書き込み行は２行下
　　ActiveSheet.Paste

　　Application.CutCopyMode = False
　　Cells(maxRow + 2, 1).Select
End Sub

お礼

完璧です。どうもありがとうございます。また、よろしくお願いします。カズミ

回答No.3

nishi6

＞ごまかしをやらずに出来ませんか。よろしくお願いします。
できると思いますが、なにぶん、何を行いたいのか全体が理解できていません。少し補足して下さい。

１．質問ではRange("A1:K2")をコピーしています。
　　しかし１行目に合計を表示しているのはH列だけです。A～Kをコピーする意味は
　　A～K全てH列と同じことをされているわけですか？（A～K列すべてH列のように合計計算をしている）

２．１、２行目を最終行にコピーしていますが、１行目と最終行に両方書いてしまえばコピーする必要が
　　なくなるような気がしますが，書き込んだらダメですか。

３．#2の補足に『最終行がセル結合していて』とありますが、質問には『シートは毎日違いますが』とあります。
　　なぜ、最終行が結合されているのでしょうか。結合セルはK1とK2みたいですが、K1とK2は最初から結合されていて、
　　１、２行目をコピーすることで結合セルが下のほうにできるわけでしょうか。

　　　K1とK2は最初結合させている
　　　　→コピーして最終行に書く
　　　　→結合セルが下に発生
　　　　→データを消して再入力（ア）
　　　　→結合セルの行が最終行になることがある
　　　　→最終行をコピーする時点でエラー発生

　　　　　のようなことでしょうか。
　　　　　そうなら、（ア）の時点で、入力する箇所の結合を解除すればいいでしょう。

４．K1とK2は結合されている必要があるとして、K1とK2をコピーする時、コピー先も結合する必要が
　　あるのでしょうか。
　　例えば、K1とK2を20、21行目にコピーする時、K1を20行目にコピーするだけではダメですか。

５．A1:K2をコピーしていますが、２行目までコピーしているのは、K1とK2が結合されているからですか。

＞L列に適当な一文字を入れL列のフォントを白にしてA列～K列として使用しています。
この意味はほとんど分かりません。

補足

説明不足ですみません。もう少し詳しく書きます。表ですが、セルを結合して２行を1行として使用しています。5,6行目が項目で7行目(7,8行目)からデータが入ります。データも貼り付けで別のシートからもらってA列からL列(1行「実際は2行」)に貼り付けます。後は、このパタンでデータが入っていきます。H列が数字のデータです。最後に合計を表示するのですが、H列の最終行に書いた後左右隣の行を飾り付けするのがマクロでは大変だと思い、A1～L2で作っておいてH1,H2(H1)セルに合計を書き最終行の次にコピーしています。A1～L2は、非表示にしています。最終行をL列とする為です。L列のみセル結合はしていません。文字も白にしています。
よろしくお願いします。

回答No.2

nishi6

やりたい事はこんなことですか。最後のコピーの意図が分かりませんが。

Private Sub CommandButton1_Click()
　　Dim maxRow As Long
　　Dim cRow As Long
　　Dim col As Integer
　　maxRow = 7
　　For col = 1 To 11
　　　　cRow = Cells(65536, col).End(xlUp).Row
　　　　If maxRow < cRow Then maxRow = cRow
　　Next

　　Range("H1") = Application.Sum(Range("H7:H" & maxRow))
　　Range("A1:K2").Copy
　　Cells(maxRow + 1, 1).Select
　　ActiveSheet.Paste

　　Application.CutCopyMode = False
　　Cells(maxRow + 1, 1).Select
End Sub

補足

思ったように出来ています。一つだけ気づいたことがあります。最終行がセル結合していてそのセル結合した中にデーターがあるとエラーがでます。つまり、A1～K2の中にセル結合があります。K1とK2で、データーも入っています。今ごまかしでA列～K列をA列～L列にして使用しています。L列に適当な一文字を入れL列のフォントを白にしてA列～K列として使用しています。ごまかしをやらずに出来ませんか。よろしくお願いします。最後に書いたマクロはここまで出来ていますと言うことです。

回答No.1

imogasi

（１）range("a:b").CurrentRegion.Lows.Countなどとやると、A列が３行まで、B列が４行まで、C列が６行までデータが入っているとして、６と出てしまいますね。お困りの点は判りました。
（２）第１行目に合計をセットするなら、
　=sum(a2:a30)関数は,例えA20～A30までに数値が入ってなくても
　エラーにはならず、A1からA19までの数値を加えてくれるので、A３０を適当な行数にしておけば、本件のような質問の必要性はなくなるのではないでしょうか。
（３）データ入力最終行の直ぐ下行にその列の合計を出すのも、各列で合計の表示セルが異なり、私の仕事の経験から、見にくいと思いますので合計行は同じ行にされては。
（４）データ入力最終行の直ぐ下行にその列の合計を出すのなら、
極く初歩的に
Sub test01()
Set wf = Application.WorksheetFunction
For j = 1 To 2 '２は適当
　For i = 1 To 50 '５０は適当
　　If Worksheets("sheet1").Cells(i, j) = "" Then
　　Worksheets("sheet1").Cells(i , j) = wf.Sum(Range(Cells(1-1, j), 　　Cells(i - 2, j)))
　　' MsgBox i
　　Exit For
　　End If
　Next i
Next j
を作って見ましたが、上手くやらないと、２回目以降が危険で、前回の合計まで加えてしまうおそれあり。毎回各列入力終了後、前回合計が残っていない前提なら、上記で、直ぐ下の行に合計が出せます。