[[20120924140903]]　『VBA 　最終行の取得方法について』（usamiyu）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『VBA 　最終行の取得方法について』（usamiyu）xl2003 win.Vista 

 いつも大変お世話になっております。
[[20120914092942]]　『複数ファイルの集計について』（じゅん）さん
 のご質問への回答内容について、わからない点があったので、（ぶらっと）さんに教えていただいていたのですが、
さらに疑問点がでてきたので、別途質問を立て直しました。
引き続き、ご教授をお願いできますか。


 End(xlup)、End(xlDown)のセルの動き方について、次のコードを示していただいて、メッセージとシート上のセルの状況を
照合して確認するようアドバイスをいただきました。
 確認したところ出てきた疑問点が以下のとおりです。


 1 TestDownとTestUpの違いに関して
　　Upは　'指定セルの上の【どこかに】値のあるセルがある場合、本当の最終行が取得可能とありますが、ctrl+↑でセルの
 動きを確認すると次のようになります。
 ctrl+↑の場合のﾚｲｱｳﾄ


 あ
 空白行
 空白行
 い
 う
 この行にカーソルを置く


 ここでctrl+↑すると、まず「う」のところにカーソルが動く　


 「あ」まで行かないと最終行の取得ができないということではないのでしょうか？


 ctrl+↓の場合でも、まず「あ」次に空白を飛ばして「い」「う」と動くので、Upのときと動きは変わらないけれど、この場合、
最終行の取得はできないということなのですよね？？動きは同じでも最終行が取得できるできないの違いがでるというところが
いまひとつわかっていません。


 2 TestOtherについて
   一つ目の場合はUsedRange.Addressが$B5$:$D100$で了解です。
   二つ目はxlCellTypeLastCellで最後のセルを取得するため、$D100$で了解です。
　 三つ目も同様に$F100$で了解。
   最後が「あれ、なんで？」というケースですよね。D100の値をクリアしたはずなのに、アドレスは$F20$にならず、$D100$の
　まま。UsedRangeとSpecialCells(xlCellTypeLastCell)、違う結果を生むんですね。なんだかUsedRangeの方がわかりやすい気が
　しますが、場合によっては、気をつけておけば、xlCellTypeLastCellの方がいい場合もあるということですか？


 以下（ぶらっと）さんのコード
 Sub TestDown()
    Dim z As Long


    Columns("A").Clear


    '指定セルを含めてその列に、それ以降何もない場合
    z = Range("A1").End(xlDown).Row
    MsgBox z


    '指定セルには値があるけど、それ以降に値がない場合
    Range("A1").Value = "ABC"
    z = Range("A1").End(xlDown).Row
    MsgBox z


    '指定セルの下に連続して値のあるセルがある場合（このケースのみ本当の最終行が取得可能）
    Range("A1").Value = "ABC"
    Range("A2").Value = "XYZ"
    z = Range("A1").End(xlDown).Row
    MsgBox z


    '指定セルの下に連続して値があり、その後、空白をはさんで値がある場合
    Range("A1").Value = "ABC"
    Range("A2").Value = "XYZ"
    Range("A4").Value = "あいう"
    z = Range("A1").End(xlDown).Row
    MsgBox z


 End Sub


 Sub TestUp()
    Dim z As Long


    Columns("A").Clear


    '指定セルを含めてその列に、それ以降何もない場合
    z = Range("A" & Rows.Count).End(xlUp).Row
    MsgBox z


    '指定セルには値があるけど、それ以降に値がない場合
    Range("A" & Rows.Count).Value = "ABC"
    z = Range("A" & Rows.Count).End(xlUp).Row
    MsgBox z


    '指定セルの上のどこかに値のあるセルがある場合（このケースのみ本当の最終行が取得可能）
    Range("A" & Rows.Count).Value = "ABC"
    Range("A100").Value = "XYZ"
    z = Range("A" & Rows.Count).End(xlUp).Row
    MsgBox z


 End Sub


 Sub TestOther()
    Dim z As Long


    Cells.Clear
    Range("B5").Value = "ABC"
    Range("D100").Value = "XYZ"
    MsgBox ActiveSheet.UsedRange.Address
    z = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row
    MsgBox z


    Cells.Clear
    Range("B5").Value = "ABC"
    Range("D100").Value = "XYZ"
    MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address
    z = Cells.SpecialCells(xlCellTypeLastCell).Row
    MsgBox z


    Range("F20").Value = "あいう"
    MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address
    z = Cells.SpecialCells(xlCellTypeLastCell).Row
    MsgBox z


    Range("D100").Clear
    MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address
    z = Cells.SpecialCells(xlCellTypeLastCell).Row
    MsgBox z


 End Sub

 本件、幅広く皆さんからアドバイスをいただきたいね。
 とりあえず、私のコメントがきっかけになっているので、露払いで。


 ヘルプでRange.Endプロパティを参照してもらいたいけど、さらっとしか書かれていないので
 ヘルプだけですべてを把握することはなかなか・・かな？


 いずれにしても、一般論は、操作としては　
 ・セルを選択
 ・Endキーをおしながら →、←、↑、↓　を押したときに、目的のセルが選択される、そのセルが Range.End(○○)
 ・○○は、xlToRight、xlToLeft、xlUp、xlDown　。
 ★ポイントは、指定セル.End によるセルの検査対象には、この指定セルは含まれない。
 　なので、たとえばA2以降のA列が空白だった場合は、A1に値があろうが無かろうが、Range("A1").End(xlDown) は
   エクセルの最終行（2003なら65536、2007以降なら1048576)を指してしまうというところが留意点。


 あわせて、その他の参考コードとして、UsedRangeを使う方法と SpcelaCells(xlCellTypeLastCell) を使う方法を
 「付録」で掲載したけど、これらは、さらに「まぎれ」があり、留意点も少なくない。
 これらについては、まず、Endプロパティを理解した後に、取り組んだ方がいいかもしれないね。


 (ぶらっと)

 >ここでctrl+↑すると、まず「う」のところにカーソルが動く　
 「あ」まで行かないと最終行の取得ができないということではないのでしょうか？


 最初にアクティブにしてあるセルを う　の下、 A5　だったとする。
 何を最終行として判定したいか、その要件にもよるけど、通常、このケースでは　う　を最終行として
 認識する処理になるね。そういう意味で、少なくとも最後の　う　のセルより下のセルをアクティブにして
 その.End(xlUp) で　う、つまり、最終行を求めることができるので、これは問題ないね。
 通常は、「う　より下のセル」として指定するセルは、エクセルの「本当の最終行のセル」
 2003 なら A65536 、2007 以降なら　A1048576。固定した数字でコードを書くのは好ましくないので
 バージョンにかかわらず A & Rows.Count と記述するね。


 >ctrl+↓の場合でも、まず「あ」次に空白を飛ばして「い」「う」と動くので、Upのときと動きは変わらないけれど、この場合、
 >最終行の取得はできないということなのですよね？？動きは同じでも最終行が取得できるできないの違いがでるというところが
 >いまひとつわかっていません。


 最初にアクティブにしてあるセルを A1 (あ　のセル）とする。
 xlDown は、指定セルの「次のセル」以降の空白ではないセルを見つける。なので例題なら、A2以降の値のあるセル、A4 の　い　が取得される。
 でも、本当の最終行は、う　の次の A5 の　う　なので、これでは正しいデータ最終行を求めることができないと言うことになるね。
 だから、途中で空白セルがある場合、その前を最終行として扱いたい時は、xlDown。
 そうではなく、とにかく、その列の一番下の値のあるセルを最終行として扱いたい場合はxlUp。　
 こんな使い分けをしよう。


 >場合によっては、気をつけておけば、xlCellTypeLastCellの方がいい場合もあるということですか


 これについては、私も幅広く皆さんのご意見をきいてみたい。
 自分としては、まず、xlCellTypeLastCell　は使わないようにしている。
 （あれ？　という状態の時にブックを上書き保存すれば、その後は正しい結果を取得できるんだけどね）


 (ぶらっと)
面白そうなんで、私も混ぜてください。

私はよく下記のようなCurrentRegionプロパティを使います。 

     With Range("A1").CurrentRegion
         MsgBox .Cells(.Cells.Count).Row
     End With

「どこが空白になっているか分からないが、最終行まではなにかのデータが入力されているリスト」
を扱うことがあったため、このような書き方のクセというか、習慣になりました。
この場合でも、A1から始まっているリストである、という前提が必要ですけどね。

正確にはCurrentregionプロパティは「空白行・列で囲まれた矩形範囲」を取得します。
だから、これが便利かどうかは、扱うデータによりますね。
（みやほりん） 

 (ぶらっと)さん、（みやほりん）さん
 ありがとうございます。お答えをじーっくり読んだつもりです。
・・が、いま、頭から煙が出そうです。自分の理解力の無さにあきれるくらいです。
この感じ、学生のときの数学の授業のときみたい。（めちゃめちゃ苦手だったんです。(笑））


 >ヘルプでRange.Endプロパティを参照してもらいたいけど 
 はい。EndのところでＦ１ヘルプ確認しています。Endプロパティの説明がでてきますが、
これでいいのでしょうか？Range.Endプロパティと同じものですか？


 「対象となるセルが含まれる領域の終端のセルを示す Range オブジェクトを返します。
End + 方向キー (↑、↓、←、→のいずれか) に相当します。値の取得のみ可能です。」とありますので、
 >Endキーをおしながら →、←、↑、↓　を押したときに、目的のセルが選択される、そのセルが Range.End(○○)　
というご説明のとおりですね。
 だから深く考えず、
 >途中で空白セルがある場合、その前を最終行として扱いたい時は、xlDown。
 >そうではなく、とにかく、その列の一番下の値のあるセルを最終行として扱いたい場合はxlUp。
 のお教えをそのまま飲み込んだほうがよさそうです。


 でもxlupの場合、（「あ」まで行かないと最終行の取得ができないということではないのでしょうか？）
 という部分はまだもやもやしています。対象となるセルが含まれる領域の終端のセルってxlUpなら、上端・・つまり「あ」
かと思っていたのですが、「う」だとすると、「あ」の行からﾃﾞｰﾀが入っていた場合、「あ」から「う」までをrangeに
したい場合はどうするのかな・・とか・・でもややこしくなるし、しつこいので、前述のとおり、飲み込みます。


 SpcelaCells(xlCellTypeLastCell) を使う方法については、今回のご教授により、「Ｅｘｃｅｌでお仕事！」というサイトの
 中の「登録したり消去したりを繰り返しているシートでは、最大でデータが収容された時点の最終セルが認知されていること
 があり」という部分が何を言っているのか理解できました。
 「なんとなく」わかったような気になっているだけで、納得していなかった部分が理解できて、これは、すっきりできました。


 CurrentRegionも（みやほりん）さんのコードをコピペして確認しました。値１がMsgBoxに表示され、1行目がカウントされて
　いるのが確認できました。


 まだまだつい最近勉強しだしたＶＢＡ・・はずかしいくらいの初心者ですが、また、次回にも、これにこりず、ご指導いただけ
 ればと願っています。


 どうもありがとうございました。


 (usamiyu)


 わかりました！！(多分？）　これが↓
 >xlupの場合、（「あ」まで行かないと最終行の取得ができないということではないのでしょうか？）
 >という部分はまだもやもやしています。対象となるセルが含まれる領域の終端のセルってxlUpなら、
 >上端・・つまり「あ」かと思っていたのですが、「う」だとすると、「あ」の行からﾃﾞｰﾀが入って
 >いた場合、「あ」から「う」までをrangeにしたい場合はどうするのかな


 Range("A" & Rows.Count).End(xlUp)で最終行を取得するんですよね。つまり「う」
 それで「あ」から「う」を取得するには"A1:A" & .Range("A" & Rows.Count).End(xlUp).Rowで
よかったんじゃないでしょうか？


 ごっちゃになっていたけれど、Endプロパティではあくまで最終行の取得。
 それを使ってある範囲を取得するときは、取得範囲の一番上のセルを指定するということでよいのでしょうか。


 私、理解できていますか？もし、また勘違いだったら教えてください。


 (usamiyu)


 >Range("A" & Rows.Count).End(xlUp)で最終行を取得するんですよね。つまり「う」


 ご名算！！


 >それで「あ」から「う」を取得するには"A1:A" & .Range("A" & Rows.Count).End(xlUp).Row


 う～ん・・・・


 「あ」を起点にして、xlDownで「う」は「取得できない」


 まず、構文として　.Range("A" & Rows.Count).End(xlUp).Row　これは
 .Range("A" & .Rows.Count).End(xlUp).Row　が正しいコード。（こうしなくても98%ぐらいはＯＫだけど）
 で、どうなるかというと、A列のエクセル最終行のセルから上に空白ではないセルをみつけ（A5の　う)
 そのセルの .Row だから 5 。


 だから、"A1:A" & .Range("A" & Rows.Count).End(xlUp).Row　は　"A1:A5" という単なる文字列になる。


 いずれにしても、例示のサンプルの場合、「う」は、「A1 あ」を起点の xlDown じゃなく、あくまで Aの最終列のセルからの xlUp　だね。


 (ぶらっと)


  >.Range("A" & .Rows.Count).End(xlUp).Row　が正しいコード。 


 ありがとうございます。Rowsの前にもピリオドを打つんですね。了解しました。


 >そのセルの .Row だから 5


 ここも了解です。


 >だから、"A1:A" & .Range("A" & Rows.Count).End(xlUp).Row　は　"A1:A5" という単なる文字列になる。


 えーと、ここもわかっていたつもりです。
 次のとおり、事前に確認していました。


 Sub confirm()
    Dim r As Long     
    With Sheets("Sheet1")
      MsgBox "A1:A" & .Range("A" & Rows.Count).End(xlUp).Row
    End With
 End Sub


 それで、
 >ごっちゃになっていたけれど、Endプロパティではあくまで最終行の取得。
 >それを使ってある範囲を取得するときは、取得範囲の一番上のセルを指定する


 と私があいまいな表現をしてしまったのは、たとえば、


 For each r in .range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)


 というように、「あ」から「う」までを[range]として取得するという意味でした。
 舌足らずで申し訳ありませんでした。


 >いずれにしても、例示のサンプルの場合、「う」は、「A1 あ」を起点の xlDown じゃなく、あくまで Aの最終列のセルからの xlUp


 という部分は理解できたと思っています。


 また、勘違いじゃなきゃいいんですけど・・。何度もお手数をおかけしてしまって、すみません。


 (usamiyu)


 了解。


 >For each r in .range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)


 ということで、これでもいいわけだけど、


 For each r in .range("A1" , .Range("A" & .Rows.Count).End(xlUp))


 こうかくと、ちょっとスマートだね。この書き方は、パターンとして覚えておくといいよ。


 Range( ) の　( ) の中を　セル1,セル2 と書くと セル1 「から」セル2 「まで」ということになる。
 で、セル1 が "A1" 、 セル2 が　.Range("A" & .Rows.Count).End(xlUp)　なので、A列データ最終セル。


 (ぶらっと)

 おはようございます！早朝からありがとうございます。


 >Range( ) の　( ) の中を　セル1,セル2 と書くと セル1 「から」セル2 「まで」ということになる。
 >で、セル1 が "A1" 、 セル2 が　.Range("A" & .Rows.Count).End(xlUp)　なので、A列データ最終セル。


 そうなんです。これが私の引っかかっていた部分です。遠回りしてしまって、すみませんでした。


 >For each r in .range("A1" , .Range("A" & .Rows.Count).End(xlUp))

なるほど！覚えます！！ありがとうございます。 

 なかなかスマートな書き方ができないので、教えていただいて、感謝しています。


 お教えくださった「上達法」、ゆっくりですが、実践しています。
 しばらく、また参考書とにらめっこ状態になろうと思います。
 最初から最後のページまで終わったら、さらりとでも全体が俯瞰できるといいなぁ。


 また、疑問点がでてきたら、登校します。
 次回もぜひ、ご指導ください。


 (usamiyu)

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.