[[20230210230545]]　『データの範囲指定マクロを教えてください。』（りりあん）　ページの最後に飛ぶ

『データの範囲指定マクロを教えてください。』（りりあん）

sheet1のA2～H2以降のデータをコピーしてsheet2のA6～に貼り付けます。
その後sheet1のI2以降のデータをコピーしてsheet2のS6に貼り付けるマクロを
組んだのですが、sheet１のデータを範囲指定するときにデータが増減する為
CTRL+↓で範囲指定した為データが1件のみの場合sheet2に貼り付けたときに
デバックを起こしてしまいました。

 Sub 範囲指定()

    ' sheet2シートに転記
    Sheets("sheet1").Select
    Range("A2:H2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.copy
    Sheets("sheet2").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False(ここのコードでデバックを起こします)

    Sheets("sheet1").Select
    Range("I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.copy
    Sheets("sheet2").Select
    Range("S6").Select
    *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

 End Sub

sheet1のA2～H2とI2が1件しかない場合でも100件ある場合でもエラーが
表示されないようにするにはどこを修正したらよいでしょうか?
ご教授ください。(◞‸◟)

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

適当に改行したのを元に戻せば良いだけ。
（適当） 2023/02/11(土) 01:17:30

＞Range(Selection, Selection.End(xlDown)).Select

あと問題はここだね。
マクロ記録した物を「組んだ」といわれても。
最終行とかで検索すれば見つかると思う。
（適当） 2023/02/11(土) 01:29:24

 データのある範囲では、A列は必ずなにかが入っているんですか？
 また、その連続した範囲よりも下には一切のデータがないんですね？

 参考にしてください。

 Sub 範囲指定()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("sheet1")
    Set ws2 = Sheets("sheet2")

    ws1.Range("A2", ws1.Cells(Rows.Count, "A").End(xlUp)).Resize(, 8).Copy
    ws2.Range("A6").PasteSpecial Paste:=xlPasteValues

    ws1.Range("I2", ws1.Cells(Rows.Count, "I").End(xlUp)).Copy
    ws2.Range("S6").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
 End Sub
（abc） 2023/02/11(土) 07:25:57

適当さま
abcさま
コメントありがとうございました。

abcさまからのコードで希望するデータ貼り付けが出来ました。
ありがとうございました

（りりあん） 2023/02/11(土) 08:21:44

書いている間に被り＆解決したようですが何点か。

■１
VBAの世界では基本的にシートやセルなど（オブジェクトといいます）はきちんと明示すれば、いちいちアクティブにしたり選択したりする必要はありません。
また、【標準モジュール】でシートの指定を省略した場合、ActiveSheetを指定したとみなされるルールです。
よって、想定外のシートを処理対象にしてしまうのを防止したり、コードの可読性向上の関連から、対象のオブジェクトを明示することをお勧めします。

この観点で提示のコードを整理しつつ修正するとこうなります。

 Sub 範囲指定_整理()
    With Sheets("sheet1")
        .Range(.Range("A2").End(xlDown), "H2").Copy
    End With
    Sheets("sheet2").Range("A6").PasteSpecial Paste:=xlPasteValues

    With Sheets("sheet1").Range("I2")
        Application.Range(.Cells, .End(xlDown)).Copy
    End With
    Sheets("sheet2").Range("S6").PasteSpecial Paste:=xlPasteValues
 End Sub

■２
＞1件しかない場合でも
既に回答がありますが、データが2行目しかない場合↓はA1048576セルを示すことになります。

 Sheets("sheet1").Range("A2").End(xlDown)

そうなると↓という意味になり、行数があふれちゃいますので【実行時エラー】が発生するのです。

 Sheets("sheet1").Range("A1048576:H2").Copy
 Sheets("sheet2").Range("A6").PasteSpecial Paste:=xlPasteValues

 ※「デバックを起こしてしまいました。」というのは適当ではありません。
    その状態は、プログラム動作中にエラー停止したので、デバッグ（プログラム修正作業）をすぐ行うのか、
    とりあえずプログラムを（強制）終了するのかExcel君から聞かれている状態です。
    質問される場合は、【発生個所】【エラー番号】【エラーメッセージ】を説明されると、回答者側で原因の
    特定がしやすくなります。

解決も既に提示がありますが、最大行から上に向かってデータがある行を探すのも有効ですし、

    Sub 研究用1()
        Stop 'ブレークポイントの代わり
        Dim 最終行   As Long

        最終行 = Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, "A").End(xlUp).Row

        Sheets("sheet1").Range("A2:H" & 最終行).Copy
        Sheets("sheet2").Range("A6").PasteSpecial Paste:=xlPasteValues

        Sheets("sheet1").Range("I2:I" & 最終行).Copy
        Sheets("sheet2").Range("S6").PasteSpecial Paste:=xlPasteValues
    End Sub

おそらく、1行目に項目行があるのだと推測しますので、CurrentRegionプロパティを使うという手もあるでしょう。

    Sub 研究用2()
        Stop 'ブレークポイントの代わり
        With Sheets("sheet1").Range("A1").CurrentRegion
            Intersect(.Cells, .Offset(1), Sheets("sheet1").Range("A:H")).Copy
            Sheets("sheet2").Range("A6").PasteSpecial Paste:=xlPasteValues

            Intersect(.Cells, .Offset(1), Sheets("sheet1").Columns("I")).Copy
            Sheets("sheet2").Range("S6").PasteSpecial Paste:=xlPasteValues
        End With
    End Sub

他にもFindメソッドで使用している最終セルを探すとか、SpecialCellsで最終セルを調べるとか思いつきますが、長くなるので割愛します。

（もこな２） 2023/02/11(土) 08:34:00

もこな２さま

お返事が遅くなりましたが、コメントありがとうございました。

マクロのことは勉強不足で・・・
いろいろとご教授頂きありがとうございました。
<(_ _)>
（りりあん） 2023/02/12(日) 08:43:36

コメント返信：

[ 一覧(最新更新順) ]

[[20230210230545]] 『データの範囲指定マクロを教えてください。』（りりあん） ページの最後に飛ぶ

[[20230210230545]]　『データの範囲指定マクロを教えてください。』（りりあん）　ページの最後に飛ぶ