【Excel VBA】データの最終行について

質問

Excel2003を使用しています。

ある一覧表形式のデータSheet1をSheet2に値のみコピーして、このSheet2を“印刷用”として、ページ設定等をして、印刷のみに使用しようと思っています。

Sheet2のI列、J列、K列には数値が入力されていて、I列、J列、K列のデータの最終行の1行下に、それぞれ6行目からデータ最終行までの合計の数式が入力されるよう、コードを追加したのですが、数式は入力されるものの、入力したい行に数式が入力されません。

マクロを実行して、数式が入力された行を見てみると、301行目に入力されていました。
Sheet1は別のシートのデータを数式により表示していて、数式が300行まで入力されているので、Sheet2の元になっているSheet1の影響(？)なのかな～？と…。こういう場合、どうすればいいでしょうか？
コードは下記のようになっています。
よろしくお願いします。

----------------------------------------

Sub 印刷用作成()
Dim i As Integer
Dim j As Long

Sheets("Sheet1").Activate
Range("B6:L6", Range("L6").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False

For i = 9 To 11
j = Application.Max(j, Cells(65536, i).End(xlUp).Row + 1)
Next
For i = 9 To 11
Cells(j, i).FormulaR1C1 = "=SUM(R2C[0]:R[-1]C[0])"
Next i
End Sub

回答No.2

onlyrom · ベストアンサー

＞Sheet1は別のシートのデータを数式により表示していて
＞数式が300行まで入力されているので、Sheet2の元に
＞なっているSheet1の影響(？)なのかな～？と…

その通りです。
Ｉ，Ｊ，Ｋ列以外にどんな値が入っているのか提示されてないのでなんですが。。。

ふつうこういった表には、他の列（Ｂ、Ｃ。。）に項目（得意先名など）が入っているはずなんですが、
それらはどうなってますか？
Ｉ，Ｊ，Ｋ列以外で表の最終行はとれないのですか？

例えば、Ｂ列で表の最終行が取れるとして。。。

'-------------------------------------------------
Sub 印刷用作成()

　Dim i As Integer
　Dim LastRow As Long
　Dim myRange As Range

　Sheets("Sheet1").Activate
　　　LastRow = Cells(65536, "B").End(xlUp).Row
　　　Range("B6:L" & LastRow).Copy
　Sheets("Sheet2").Activate
　　　Range("B6").PasteSpecial xlPasteValues
　Application.CutCopyMode = False

　For i = 9 To 11
　　　Set myRange = Range(Cells(6, i), Cells(LastRow, i))
　　　Cells(LastRow + 1, i).Value = WorksheetFunction.Sum(myRange)
　Next i

End Sub
'-------------------------------------------------

以上。

お礼

回答ありがとうございます。

>Ｉ，Ｊ，Ｋ列以外で表の最終行はとれないのですか？

Sheet2のデータは全てSheet1から数式で表示しているので、Sheet2からはデータの最終行を取得できませんでしたが、大元のSheetを利用することで、データの最終行を取得できたので、これを元にうまく処理することができました。
ありがとうございました。

回答No.3

Wendy02

こんばんは。

この部分は、ユニークですね。こういう方法もよいですね。

For i = 9 To 11
j = Application.Max(j, Cells(65536, i).End(xlUp).Row + 1)
Next

シート2のI列から3列の最終行の次の行を決めるためですが、ただ、コード自体が、シート・モジュールに書いてしまっているかもしれません。

Sheet1 のシートモジュール(ローカル）に書くと、Cells　の親オブジェクトは、いくら、Sheetを変えても、何も上位オブジェクトを書かないと、Sheet1 の場所を探してしまいます。
Excelの場合は、特殊な場合を除いて、標準モジュールを使います。

あえて、シートモジュールに書くためには、親オブジェクトを意識して、例えば、以下のように、With ステートメントを入れてみたらいかがでしょうか。そうすると、狂いが少なくなります。(中に書くのは、.Range, .Cells というように、オブジェクト等の前に「.(カンマ）」が付きます。）

Sheets("Sheet2").Select　'あってもなくてもよい
With Worksheets("Sheet2") '←With ステートメントを加える
　.Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False

For i = 9 To 11
j = Application.Max(j, .Cells(65536, i).End(xlUp).Row + 1)
Next
For i = 9 To 11
　.Cells(j, i).FormulaR1C1 = "=SUM(R2C[0]:R[-1]C[0])"
Next i
End Sub

End With

-----------------------------
'私案：(標準モジュール、またはシートモジュール）

Sub 印刷用作成2()
　　Dim i As Integer
　　Dim j As Long
　　Dim r As Range
　　With Worksheets("Sheet1")
　　　　Set r = .Range("B6", .Range("L6").End(xlDown))
　　　　Worksheets("Sheet2").Range("B6").Resize(r.Rows.Count, r.Columns.Count).Value _
　　　　　= r.Value　'値コピーの場合
　　End With
　　With Worksheets("Sheet2")
　　　　For i = 9 To 11
　　　　　　If j <= (.Cells(65536, i).End(xlUp).Row + 1) Then
　　　　　　　　j = .Cells(65536, i).End(xlUp).Row + 1
　　　　　　End If
　　　　Next
　　　　If j > 1 Then
　　　　　　.Cells(j, 9).Resize(, 3).FormulaLocal = "=SUM(R2C:R[-1]C)"
　　　　End If
　　End With
　　Set r = Nothing
End Sub

お礼

Wendy02 さん、こんにちは。
いつもありがとうございます。

>コード自体が、シート・モジュールに書いてしまっているかもしれません。

コードは標準モジュールに記述していますが、やはり、Sheet2の元になっているSheet1の影響のようです。
Sheet2のデータは全てSheet1から数式で表示しているので、そのSheet1の元になっているSheetを利用して、データの最終行を求めることができました。

同様の処理をするコードでも、いろんな書き方があるのですね。
勉強になります。ありがとうございました。

回答No.1

higekuman

Sheet2の、(65536,9)、(65536,10)、(65536,11)それぞれで、そのセルをアクティブにした状態から Ctrl+↑で、どのセルにジャンプするか試してみましょう。

お礼

アドバイスありがとうございます。

Ctrl + ↑で、どのセルにジャンプするかで調べることができるのですね。大変参考になりました。
やはり、Sheet2の元になっているSheet1の影響で、数式を入力したい行に入力できなかったようです。

今回は、ANo.2の方からアドバイスをいただいた“別の列からデータ最終行を取得する”方法で、うまくいきました。
ありがとうございました。