[[20180904101912]]　『【マクロ】指定の列を軸に、空白がある列の最終行』（トイレの女神）　ページの最後に飛ぶ

『【マクロ】指定の列を軸に、空白がある列の最終行までを範囲選択してコピーする』（トイレの女神）

こんにちは(*^^*)

シート1の行「D2：G2」の最下行までをコピーしたいのですが
最下行までにいくつか空白セルがあり、
最下行まで選択することが出来ないため、
空白がないA列の最下行と同じ行まで
D2：G2の最下行まで選択し、コピーがしたいです。
(空白があるデータを使うなって話はおいといて...)

いろいろ考えてみたのですが
うまくコード化出来ず、、、
(マクロの記録も使いましたが有効な手立て見つからず)
ご教示いただけますと幸いです。。

(Cells(Rows.Count, "A").End(xlUp)を使ったり
A列最終行を変数や、Forで最終行まで調べて...とか？
となんとなくで思いつくのですが全くコードが思いつかず...
5日もかけてこれなので正直お手上げです...)

マクロ勉強中でして自分で読んで
意味が理解できるようになりたいので
可能であればインデント等
付け加えていただけると助かります。

お手数ですがお力添えください(;_;)

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

    Sub 最終行1()
        Dim Lr As Long
        With ThisWorkbook.Sheets("Sheet1")
            Lr = .Range("A1").End(xlDown).Row
            '又は Lr = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range(.Range("D2"), .Cells(Lr, "G")).Copy
        End With
    End Sub

End(xlDown) がシート上で Ctrl + ↓
End(xlUp) がシート上で Ctrl + ↑

 です。

 データに空白がある場合は End(xlDown) は NG。
 シートの下のほうにぽつんと消し忘れデータがあったりすると End(xlUp) はNGです。

 また範囲選択は色々やり方がありますが、
 変数を使うなら Range(始点セル , 終点セル) の書き方がわかりやすいと思います。

（TAKA） 2018/09/04(火) 10:33

 ないときにエラーになるのと、2行目からってのが気になりますけど
 With Sheets("Sheet1")
  Intersect(.Range("D:G"), .UsedRange).Offset(1).Copy
 End With
 と、すればどうでしょうか？
（SoulMan） 2018/09/04(火) 10:37

分かりやすく日本語にすると

.Range("A1").End(xlDown).Row

 A1 から Ctrl + ↓ を押して止まったセルの 行番号

.Cells(.Rows.Count, "A").End(xlUp).Row

 Rows.Count は Excelの行の数(Excel2013なら1048576)です。
 つまり A1048576 から Ctrl + ↑ を押して止まったセルの 行番号

という意味になります。
（TAKA） 2018/09/04(火) 10:41

Range("D2:" & "G" & Range("A" & Rows.Count).End(xlUp).Row).Copy

（mm） 2018/09/04(火) 10:48

 DからG列でどの列が最終行か分からない

 ってことかなと、、、

 Intersectは重なる範囲
 UsedRangeは名の通り使用した範囲
 つまりD:G列と使用した範囲の重なる範囲
 をコピーすればいいんじゃないかと、

 でも、UsedRangeは不安定なのであまりお勧めはできませんけどね(^^;

 後は、Findで逆から検索するとか、

 列が少ないのであれば一列づつ下からUPして最大行を取得する

 とかでしょうか？

 そういえば昔、苦戦したのを思い出しましたよ(笑)
[[20180316060737]]
（SoulMan） 2018/09/04(火) 10:54

SoulManさんの案はスマートですが、A列を見ていない点（これはD:G列だけでも十分そうですが）と、D列等が全部空欄の場合にコピー先頭列が減ってしまい、張り付ける際にデータが全部ずれてしまう可能性がある点から、使用シーンを選びますね。常にほとんどのセルはデータで埋まっている、とかならOKです。
TAKAさんとmmさんの CTRL+↑ の動作を利用する案は、よく使われる方法なのでお薦めです。

別案なぞ。ステップ実行する事で、ロジックは理解できるかと思います。

 Sub test()
    Dim cw As String
    Dim iw As Long

    With Sheets("Sheet1")
        cw = .Range("A:A").SpecialCells(xlCellTypeConstants).Address
        iw = InStrRev(cw, "$")
        iw = Mid(cw, iw + 1)
        .Range("D2", .Cells(iw, "G")).Copy
    End With
 End Sub
（???） 2018/09/04(火) 11:10

 すみません
見逃してました^^;
 >空白がないA列の最下行と同じ行まで

（SoulMan） 2018/09/04(火) 11:15

みなさんありがとうございます（泣）

＞2行目からってのが気になりますけど
上記に関して、このマクロを用いて
表から表へデータを追記していくマクロを作成しております。
1行目には見出しが入るので
2行目のデータからコピーしたいと思ってました。

今回はTAKAさんに提示して頂いたコードで
続けてみようと思います！
(別のコードでWithステートメントを使用していたため親近感？が＼(^o^)／)

TAKAさん
丁寧な解説ありがとうございます。
なんとなく仕組みがわかったような気がします。
頂いたコードを実行したところ、
はじめのうちは問題なく動作していたのですが
何回か使ったところ、下記部分が黄色くなり、エラーになってしまいました。
　Lr = .Range("A1").End(xlDown).Row
エラーは↓
実行時エラー ‘424’: オブジェクトが必要です。

はじめの数回は問題なく動いていたのに、、、(・・?
（トイレの女神） 2018/09/04(火) 13:38

変数宣言のところから黄色くなったとこまで全部見せてもらえば分かるかもしれません。。
（TAKA） 2018/09/04(火) 13:54

TAKAさん

コピペしてそのまま貼り付けていたので
おそらくご提示いただいたコードと差異ないかもしれませんが
念の為、、、、

Dim Lr As Long

        With ThisWorkbook.Sheets("Sheet1")
            Lr = .Range("A1").End(xlDown).Row　'ここが黄色くなります。。
            '又は Lr = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range(.Range("D2"), .Cells(Lr, "G")).Copy
        End With

mmさんので動くか試してみます！！
（トイレの女神） 2018/09/04(火) 15:34

mmさんので問題なく動作しました！
ありがとうございます＼(^o^)／

でもなぜTAKAさんのだと動かないのでしょうか。。。

初心者ですが拝見した感じでは
問題なさそうなのに...
（トイレの女神） 2018/09/04(火) 15:41

コメント返信：

[ 一覧(最新更新順) ]

[[20180904101912]] 『【マクロ】指定の列を軸に、空白がある列の最終行』（トイレの女神） ページの最後に飛ぶ

[[20180904101912]]　『【マクロ】指定の列を軸に、空白がある列の最終行』（トイレの女神）　ページの最後に飛ぶ