マクロで、行追加後もオートフィル＆並べ替えができるようにしたい

質問

マクロでわからないことがあるので教えていただけないでしょうか？

(1)計算式を入力
(2)オートフィルでコピー
(3)並べ替え

このようなマクロを作りたいのですが、行を追加してもちゃんとオートフィルと並べ替えができるようにしたいのです。
過去の「教えてgoo」に載っていたものをコピーしてＶＢに貼り付けたら、オートフィルはできるようになったのですが、並べ替えがうまく実行されません。
どうも範囲がおかしいように思うのですが、もともとＶＢ言語の意味がわかっていないので、どう変更すればいいのかさっぱりわかりません・・・

以下は、現在のマクロの記述です。セルＩ７を基準にして、Ｈ列も一緒に並べ替えたいと思っています。Ｈ列とＩ列は行が増えたり減ったりします。

勉強不足ですいませんが、教えてください。よろしくお願いいたします。

-------------------------------------------------------
Sub 計算→並べ替え()
'
' 計算→並べ替え Macro
' マクロ記録日 : 2006/12/1 ユーザー名 : ○○
'
ActiveCell.FormulaR1C1 = "=MOD(RC[-1],5)"
Range("I7").AutoFill Destination:=Range("I7", Range("H65536").End(xlUp).Offset(, 1))
Range("I7").Select
Range("H6:I13").Sort Key1:=Range("I7"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End Sub
-------------------------------------------------------

回答No.1

FEX2053 · ベストアンサー

4行目のこの部分が問題です。

>Range("H6:I13").Sort Key1:=Range("I7"),...

これだと"H6:I13"の範囲しかソートしません。
この部分が所定の範囲になるように修正する必要があります。

とりあえず、オートフィルにした範囲をソートするならば、

>Range("I7:H65536").Select
>Range(Selection, Selection.End(xlUp)).Select
>Selection.Sort Key1:=Range("I7"),...

こんな感じで範囲設定の式を2行追加、Rangeの行を修正・・・ですかね。
他にももっと上手い方法はありそうですけど・・・。

お礼

ご返答ありがとうございました。お礼がおそくなってすいません。
教えていただいたとおりにコードを変更したら、うまくいきました！
思い通りのマクロが組めて感激です♪
ありがとうございました。
また何かありましたら教えてください。よろしくお願いいたします。

回答No.2

Wendy02

こんにちは。

もし、本当に、「とりあえず」でしたら、
　　Range("H6:I13").Sort
　　　　　↓
　　Range("H6").Sort
　　(必ずしも、上手くいくとは限りません。CurrentRegion と同じ機能がありますから、不要な部分も巻き込んでしまうことがあります。)

とすればよいのですね。ただ、一般的に、問題は、"Header:=　xlGuess" の部分になりますね。つまり、このコードを見る限りは、ヘッダーが付いているようですから、そこは、"Header:=xlYes" ということになりそうです。

それから、
　　ActiveCell.FormulaR1C1 = "=MOD(RC[-1],5)"
　　　　　↓
　　Range("I7").FormulaR1C1 = "=MOD(RC[-1],5)"

となるようです。

>Range("I7").Select

これは必要ありません。

ただ、全体的になんとなく奇妙な感じがします。
理由は、ActiveCell.FormulaR1C1 = "=MOD(RC[-1],5)"　という数式を入れる部分にあります。マクロの場合は、相対参照式なので、最初から数式を代入してしまってもよいかもしれません。

例：
Sub TestPrc()
Range("H7", Range("H65536").End(xlUp)).Offset(, 1).FormulaR1C1 = "=MOD(RC[-1],5)"

Range("H6", Range("H65536").End(xlUp)).Resize(, 2).Sort　_
　　　　 Key1:=Range("I7"), _
　　　　　 Order1:=xlAscending, _
　　　　　 Header:=xlYes, _
　　　　　 OrderCustom:=1, _
　　　　　 MatchCase:=False, _
　　　　　 Orientation:=xlTopToBottom, _
　　　　　 SortMethod:=xlPinYin, _
　　　　　 DataOption1:=xlSortNormal
End Sub

私の想像の範囲で作ったので、実際には上手くいかないかもしれません。また、もう少し省略できるけれども、いやらしくなりそうな気がしましたので、やめました。それから、みやすくするために、以上のようなコードの並べ方をしたほうがよいです。

お礼

ご返答ありがとうございました。お礼がおそくなってすいません。
ご指摘のとおり最初の計算式は、マクロを記録する前にセルＩ７を選択してしまったので、次からマクロを実行するときにセル番地がずれてしまうところでした。
投稿した後で気づいたのですが、それを訂正するのに苦労したので、正しいコードを教えてくださって助かりました。
ありがとうございました。
ヘッダーの部分などは、せっかく教えていただいたのですが、よくわからなかったので、勉強して理解できるようにがんばります。すいません(>_<)
また、コードをそのままコピーしたので、見にくくなってしまってすいませんでした。次からは気をつけます。

おかげで思い通りのマクロを組むことができました。ありがとうございました。
また何かありましたら、教えてください。ありがとうございました。