オートフィルのマクロを作りたい

質問

ある列に複数のデータがある場合、その一番上の右隣のセルに数式などを入力後、そのセルの右下の小さい四角をダブルクリックすると、左の列のデータある一番下までオートフィルされますよね。
これのマクロを組みたいのですが、単純に出来ません。
普通通りマクロの記録をすると、実際に入力されていたセル番号までのマクロとなってしまい、データを追加した場合意味が無くなります。
具体的に説明すると、
A1からA10までそれぞれデータがある場合、
B1に数式を入力後、オートフィルでB10までコピー。
これをマクロで組もうとすると、B10までのフィルが組まれてしまい、A11～A20までデータを追加後マクロを実行してもB10までのフィルしか実行されません。
「左の列のデータがあるところまで」というマクロは組めないのでしょうか。

回答No.1

ja7awu · ベストアンサー

B1をオートフィルする範囲は、A1～A65536でCtrl+↑したデータ範囲の右隣の列ということになり

Sub test()
　　Range("B1").AutoFill Destination:=Range("B1", Range("A65536").End(xlUp).Offset(, 1))
End Sub

という感じで如何でしょうか。

お礼

ありがとうございました。
これで目的の操作のマクロを組めるようになりました。

回答No.6

Wendy02

#2 の私のコードは、A1～Aのデータのあるところまでの範囲を、B1の式を反映させたい、というコンセプトに対して、左の列で範囲を取ってから、その範囲自体を右に移すというロジックです。

例えば、実際にVBAのコードを書いていく場合は、

Set myRange =Range("A1", Range("A65536").End(xlUp))

としておけば、それを１つの長さ（領域の範囲）として、

myRange.Offset(,i)
で、列の移動を、For ～To .. Next で、そのまま中身を換えずに使い回しすることも可能なのです。

もしも、A1 ～ A列の最後尾（例 A10) の、B列までの範囲(A1:B10)を取りたい場合は、

Range("A1", Range("A65536").End(xlUp).Offset(,1))

ですね。一応、ご参考までに。

回答No.5

quit123

ja7awuさんへ

ja7awuさんを始め数人の方の回答は必ず眺めるようにしているのですが、No4.でちょと気になる発言がありましたので一言。

＞ちょっと類似コードをUPする意図は、解りません。
＞当方に、ポイントは一切必要ありませんので、他の方へ差し上げてください。

こういうのは読む方としてはあまり愉快なものではありません。特にja7awuさんの回答を楽しみしている当方などにとっては。

今回の場合はちゃんと別案が提示されてるので別にどうのこうのという問題でもないような気もしますが。

もしそれを言うなら、参考ＵＲＬの問題で、他の回答と全く同じ回答をされてますが（しかも他の回答より拙い、また別案もない）、その意図をお尋ねしたくなります。(^^;;;

それから、何にもならないポイントなんか誰も欲しくはないでしょう。(^^;;;;

ja7awuさんほどの方がこれくらいのことで、と今回はちょと吃驚しましたので一言書いてみました。

これからも目から鱗の回答を期待しています。

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1406981

回答No.4

ja7awu

#1 ですが、類似のコードがレスされていますので、shibiseiさんの混乱をさけるため
補足いたします。

説明文の表現が まずかったところもあったようですが・・・

Range("B1").AutoFill Destination:=Range("B1", Range("A65536").End(xlUp).Offset(, 1))

のコードについて、オートフィルする範囲の先頭は、計算式が入力されているB1ですから、
ゴードの左と同じで構いません。　別にA1をオフセットする必要は、ありません。

それで、オートフィルする範囲の最終については、

>「左の列のデータがあるところまで」というマクロは組めないのでしょうか。

という質問ですので、その「左の列のデータ最終」は、A65536でCtrl+↑ した時のセルの右隣
ですので、その部分のコードが、Range("A65536").End(xlUp).Offset(, 1)　となっています。

ちょっと類似コードをUPする意図は、解りません。

当方に、ポイントは一切必要ありませんので、他の方へ差し上げてください。

回答No.3

hiroyukitank

No.1,2の方とは視点を変えてみました。
最終行を変数で取得し，cellでb1のセルから最終行のセルまでフィルハンドルをドラッグさせてみました。
お試しください。

Sub Macro1()
ActiveCell.SpecialCells(xlLastCell).Select
lasty = ActiveCell.Row 'lastyは変数
Range("b1").Select
Selection.AutoFill Destination:=Range(Cells(1, 2), Cells(lasty, 2)), Type:=xlFillDefault
End Sub

お礼

ありがとうございました。
確かに私の質問内容でのマクロは組めました。
ただ私のマクロに対する理解が乏しいため、他のセルでの適用がうまくできませんでした。
Range("b1").Select
のB1の部分を修正するだけでは駄目なのでしょうか。
＃1,2の方の回答で目的は果たせましたので、今後勉強したいと思います。

回答No.2

Wendy02

Wendy02 です。
あまり評価を受けないかもしれませんが、

Sub TEST()
　Range("B1").AutoFill Range("A1", Range("A65536").End(xlUp)).Offset(, 1)
End Sub

たぶん、こういうことでしょうね。
ただ、あまり、こういう AutoFill 自体は必要ないかもしれませんね。VBAのコードらしい書き方ですと、こんな書き方になるかと思います。

Sub TEST2()
With Range("A1", Range("A65536").End(xlUp)).Offset(, 1)
　　.Formula = Range("B1").Formula
End With
End Sub

お礼

ありがとうございました。
教えていただいた内容でも#1の方と同様に目的の操作のマクロが組めました。