エクセル マクロで数式の検索範囲を指定するには

質問

いつもお世話になります。

別シートに貼り付けたデータを、マクロで自動的にSUMIFやCOOUNTIF関数で集計しているのですが、現在は範囲を3～65536行までにしています。（上2行を見出しにしているので）
これだと計算に非常に時間がかかるのですが、貼り付けたデータの行数のみを数式の範囲にするか、もしくは同じようなことをVBで行うことは可能でしょうか。

以上宜しくお願い致します。

回答No.4

Samurai-Jack · ベストアンサー

＃１です。

こんなのではどうでしょうか？意図されているのとちがいますかね？？

Dim a As String
Dim i As Integer
'
ActiveCell.SpecialCells(xlLastCell).Select
a = ActiveCell.Row
For i = 1 To 30
ActiveCell.FormulaR1C1 = "SUMIF('Sheet1'!3C4:R" & a & ",RC2,'Sheet1'!R3C11:R" & a & "C11))"
Next

お礼

まさにこれです。問題解決しました。
ありがとうございました。

回答No.5

ta123

集計表シートがsheet2としますと、以下のようになるのではと思います。前後の処理が不明なため、都合の悪いところがあるかもしれません。また、埋め込みたい関数は再チェックしてください。

'　sheet1の最終行を求める（A列でチェック)
Worksheets("sheet1").Select
Range("A65536").End(xlUp).Select
MyRow = ActiveCell.Row　　　'行番号を記憶

'アクティブシートを集計シートに切り替える
Worksheets("sheet2").Select

'埋め込む関数を編集する
MyStr = "=SUMIF('Sheet1'!R3C4:R" & MyRow & "C4,RC2,'Sheet1'!R3C11:R" & _
MyRow & "C11)"

For i = 1 To 30
ActiveCell.FormulaR1C1 = MyStr
・・・・

お礼

いただきました回答をもとに、問題解決しました。
ありがとうございました。

回答No.3

imogasi

＞マクロで自動的にSUMIFやCOOUNTIF関数で集計しているのですが
この意味が良く判りませんが。
合計値をセットする（出す）セルは変動するのでしょうか。
＝で代入している（下記）のでしょうか。
Ｗｏｒｋｓｈｅｅｔ関数を使っているのでしょうか。
式をＦｏｒｍｕｌａで埋めこんでいるのでしょうか。
マクロを使えばSUMIFやCOOUNTIF関数は必ずしも必要なく他のやり方で合計が出せますから。
ただ関数以外を使うと、データが増えても再計算してくれませんが。それで関数を使っているのかも。
最下行はd = Range("A1").CurrentRegion.Rows.Count
で捉えるので
Sub test02()
d = Range("A1").CurrentRegion.Rows.Count
Cells(10, 1) = "=Sum(a1:a" & d & ")"
End Sub
のようにすればよいかと思います。

＞質問の中の「貼り付けた」に拘ると
ＶＢＡ程度では、「コピーをした」や「貼り付けをした」、という動作（イベント）を掴まえることが難しい。
（クラスという考えを持ちこんで出来るかどうか。）
貼りつけた後で、一番最下行（か最右行）を知ることは出来ますが、縦列データのある途中までに貼りつけられると
お手上げです。
ボタンを貼りつけ、コピー範囲を指定した段階でボタンを
押すと、下記コードを入れておくと、指定範囲を捉えることが出来るのですが。
Private Sub CommandButton1_Click()
MsgBox Selection.Address
End Sub
関数を使えば、データが貼りつけられた段階で、データが変るので、再計算機能が働きますから、上記を意識しなくてもよいですが。

お礼

補足を記入した後に、他の方々からも回答をいただきましたが、最初にいただいたお答えでも問題解決できていましたね。
いつもありがとうございます。

補足

ありがとうございます。
質問が言葉足らずで申し訳ございません。
別シートに集計表があり、
For i = 1 To 30
ActiveCell.FormulaR1C1 = "SUMIF('Sheet1'!3C4:R65536C4,RC2,'Sheet1'!R3C11:R65536C11))"
・・・・
という形で集計するようにマクロを組んでいるのですが、この、「3～65536行」という範囲を、例えば2000行までしかデータが貼り付けられていなければ、「3～2000行」という数式に変更できないかという質問です。
尚、データは3行目から連続して貼り付けられているものとしています。
申し訳ないですが、ご存知でしたら、宜しくお願い致します。

回答No.2

Samurai-Jack

途中に空白行がある場合は、以下の方法も有効だと思います。

ActiveCell.SpecialCells(xlLastCell).Select

これはCtrlキーとEndキーを同時に押して移動するのと同じ動きになります。

回答No.1

ta123

途中行に空白が無い場合、最終行を以下で知ることができます。

Range("A1").End(xlDown).Select
または　Range("A65536").End(xlUp).Select

これはＡ１セルをアクティブにしてCtrlと↓（↑）キーを押した時の動作と同じです。この結果選択されたセルの行番号を用いればいいと思います。

お礼

他の方々からの回答と併せて、問題解決できました。
ありがとうございました。

補足

ありがとうございます。
質問が言葉足らずで申し訳ございません。
別シートに集計表があり、
For i = 1 To 30
ActiveCell.FormulaR1C1 = "SUMIF('Sheet1'!3C4:R65536C4,RC2,'Sheet1'!R3C11:R65536C11))"
・・・・
という形で集計するようにマクロを組んでいるのですが、この、「3～65536行」という範囲を、例えば2000行までしかデータが貼り付けられていなければ、「3～2000行」という数式に変更できないかという質問です。
いただいた回答の内容でしたら、ActiveCellの行番号を取得するということだと思うのですが、そこからどのように数式に反映すれば良いかが分からないのです。
申し訳ないですが、ご存知でしたら、宜しくお願い致します。