[[20081006141639]]　『マクロによる行追加と書式コピーについて』（クララ）　ページの最後に飛ぶ

『マクロによる行追加と書式コピーについて』（クララ）

こんにちは。

VBA初心者で初めて質問書込みさせて頂きます。

Excelで以下のようなデータがあります。

　　　　Ａ　Ｂ　Ｃ

１行目　100　30　70

２行目　200　120　80

３行目　145　45　100

　　　　　・

マクロで１行追加を何行か行って、追加した行にデータ入力後、

さらに行追加のマクロを作ってみたところ、

３行目と４行目の間に追加したデータが挿入されてしまうのを、

追加した行の直後に挿入するマクロは以下のVBAで行けましたが、

Sub マクロ行追加()

Range("A1").Select

Selection.End(xlDown).Select

Selection.Offset(1, 0).Select

Selection.EntireRow.Insert

End Sub

マクロで１行追加後、書式もコピーするには上記VBAをどう変えれば良いのか分からなくて、詰まってしまったので、どなたかご教授下さい。宜しくお願いいたします。

因みに書式コピーのVBAは以下のような構文になっていると思いますが、

それを上記のどの部分に追加した上で、どう変えれば良いのか分からなくて困っています。

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False

 ご本人以外のレスが付いて無いようなので
 PasteSpecial はInsert後にコピーを宣言した後に入れれば良いかと
 ご参考までに
 Sub TestMacro()
 Dim myRow As Long
 '------------------------------------------------
 '挿入行の取得
 'セル結合・A列の空白セルを避ける為に↓がお勧め
 myRow = Range("A65536").End(xlUp).Row + 1
 '------------------------------------------------
 '行挿入
 Range("A" & myRow).EntireRow.Insert Shift:=xlDown
 '------------------------------------------------
 '挿入行の1行上の行をコピー
 Range(myRow - 1 & ":" & myRow - 1).Copy
 '------------------------------------------------
 '挿入行に貼り付け
 '（「貼り付け」で「数式」、「演算」を「しない」で貼り付け）
 Range("A" & myRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
 '        SkipBlanks:=False, Transpose:=False
 '         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 '         ↑の2個（「空白セルを無視する」・「行列を入れ替える」）は
 '　　　　 初期値が「False」の為、今回は未指定
 '------------------------------------------------
 'コピーモードの解除
 Application.CutCopyMode = False
 End Sub

 ただし
 　　Ａ　　　Ｂ　　　Ｃ
 １　１００　３０　　=A1-B1
 ２　２００　１２０　=A2-B2
 ３　１４５　４５　　=A3-B3
 　　　　　・

・

 　　　　　・
 45　○△□商社
 46　◎▽事業部
 47　Ｘ◇さま

 等の別グループのデータ等が有るという場合は
 'セル結合・A列の空白セルを避ける為に↓がお勧め
 myRow = Range("A65536").End(xlUp).Row + 1
 を
 'シートの途中に別データブロックが有るなら↓
 myRow = Range("A1").End(xlDown).Row + 1
 にして下さい　（力技）

おはようございます、クララです。
上記VBAを以下のように少し改造した後にマクロ実行してみたところ、
期待通りの結果が得られました。

'挿入行の1行上の行をコピー

 Range(myRow - 1 & ":" & myRow - 1).Copy
 '------------------------------------------------
 '挿入行に貼り付け
 '（「貼り付け」で「数式」、「演算」を「しない」で貼り付け）
 Range("A" & myRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
 '      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 '         ↑の2個（「空白セルを無視する」・「行列を入れ替える」）は
 '　　　　 初期値が「False」の為、今回は未指定
 '------------------------------------------------

本当、どうもありがとうございました！助かりました。
VBAプログラミングは初心者ですが、コツコツ頑張って行きたいと思います！

 たびたびすみません。
Excelで以下のようなデータがあって、
「Ｃ列」は「Ａ列」から「Ｂ列」を引いたものを表示しています。
　
　　　　Ａ　 Ｂ　 Ｃ
１行目　100　30　 70
２行目　200　120  80
３行目　145　45　 100
　　　　　・
　　　　　・
　　　　　・
20行目　200　50　 150
　　　　　・
　　　　　・
　　　　　・
　
入力データが大量にある為に「Ｃ列」の所だけを自動計算出力させるようにしたく、
以下のようなVBAだと、
 -----
Sub 引き算()
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C20"), Type:=xlFillDefault
    Range("C2:C20").Select

End Sub

 -----
　
「C2」（２行）から「C20」（20行）までの「Ｃ列」しか、
自動計算出力出来ません。
　
昨日、上記で、ご教授頂いた「マクロによる行追加と書式コピー」（コマンドボタン）において、
「C20」（20行）の下に新たに１行挿入し、
　
その行の「A列」「Ｂ列」にデータを入力して、
「C20」下の「C21」のセルでも自動計算出力させるようなマクロを作成したいのですが、
　
上記VBAをどう変えたら良いのか分からないので、
どなたかご教授宜しくお願いいたします。
　
マクロによる行追加と書式コピーでも、自動計算機能が備わったままにしたいです。

 よく分かっていませんが・・・・
 上をコピーした物を普通に貼り付けて、
 定数部分（myRowのA:B列）だけDelete
 では駄目なのですかね？

 それとも、
 　新しい行は挿入した時点では数式が無く
 　A:B列を入力した時に数式を入れたい
 と言う事なのですかね？

 (HANA)

 １行挿入時後のコピーの際に数式もコピーしてませんか？
 挿入時に新たに数式を入れたいとかならですか？
 >'------------------------------------------------
 >'コピーモードの解除
 >Application.CutCopyMode = False
 の次に
 '------------------------------------------------
 '挿入行に関数の設定
 Range("C" & myRow).FormulaR1C1 = "=RC[-2]-RC[-1]"
 を追加して下さい。

 ただHANA先生のおっしゃる方法がスマートかと思います
 Range(Cells(myRow, 1), Cells(myRow, 2)).ClearContents
 （力技）

力技さんとHANAさん、ご教授どうもありがとうございます！
以下のVBAで行けましたが、

Sub 行追加ボタン()

Dim myRow As Long

 '挿入行の取得
 myRow = Range("A65536").End(xlUp).Row + 1

 '行挿入
 Range("A" & myRow).EntireRow.Insert Shift:=xlDown

 '挿入行の1行上の行をコピー
 Range(myRow - 1 & ":" & myRow - 1).Copy

 '挿入行に貼り付け
 Range("A" & myRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

End Sub

ひとつ問題があります。

上記VBAをコマンドボタンに登録して、マクロを実行してみた所、
行追加が完了するのに（マウスポインタが砂時計から矢印に戻るのに）
時間がかかります。（４秒ぐらい）

多分、上記VBAの挿入行の取得の所で、65535行目までを検索している為に
マクロ実行に時間がかかっていると思われます。

せめて、１～２秒ぐらいで完了したいのですが、
どうしたらいいのか分からないので、どなたかご教授宜しくお願いいたします。(T_T)

また、他に色々な作業に追われていて、ドタバタしていた為、
返事が遅くなってすみませんでした。

 お疲れ様です
 ＞時間がかかります。（４秒ぐらい）
 あれ？そんなに掛かりますか＞＜
 ん～　画面の更新を止めてみるとどうでしょう
 '画面更新の停止
 Application.ScreenUpdating = False

 ここにマクロを記入

 '画面更新の開始 
 Application.ScreenUpdating = True

 で多少は早くなりましたか？
 （力技）

 ＞以下のVBAで行けましたが、 
 これって何ができるコードですか？
 結局　数式のコピーは行われていない様ですが・・・。

 時間がかかるのは、再計算が行われるため
 かもしれませんね。

 計算を手動に切り替えてやってみたときに
 どのくらいかかるか調べてみるのが良いかもしれません。

 また
 どうせ上からコピーしてきて貼り付けるなら
 行の挿入コピーでも良い様に思います。
 コードは記録マクロで得られるので
 そちらが参考にできると思います。

 ちなみに、半角マイナスを４つつなげると
 水平線になってしまいます。
 本文部分とコード部分を分けるだけの目的であれば
 半角マイナスの前に半角スペースを一つ
 入れてもらうのが良いと思います。

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20081006141639]] 『マクロによる行追加と書式コピーについて』（クララ） ページの最後に飛ぶ

[[20081006141639]]　『マクロによる行追加と書式コピーについて』（クララ）　ページの最後に飛ぶ