HOME > Q&A > Excel （VBA）

Excel （VBA）
Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2019)
最終行の取得について
投稿日時: 23/05/22 14:52:29
投稿者: tamtam

現在vba習得のため他サイト様に掲載されている練習問題を解いているのですが、
最終行の取得についてお尋ねします。
 
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("●●●")
Set ws2 = Worksheets("△△△")
For i = 2 To ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row
 
問題の内容としては、ws1のセルに日付や人名を入力すると、その条件に合うデータをws2から抜出し、
ws1上に羅列させよというものです。
上記の記述について、『ws2.Cells(ws1.Rows.Count, 1)』の部分が理解できず悩んでおります。
これはws2の最終行を取得しているのでしょうか？それともws1の最終行を取得しているのでしょうか？
何故シートを跨ぐような記述になるのかがわかりません。
どうぞよろしくお願いいたします。
回答
投稿日時: 23/05/22 15:55:49
投稿者: hatena
投稿者のウェブサイトに移動

Rows.Count はシートの最大行数を返します。どのシートの Rows.Countも同じです。
ですので、
 

ws2.Cells(ws1.Rows.Count, 1)

 
というように異なるシートを参照してもエラーにはなりません。
ws1.Rows.Count はあくまで単なる数値ですので。
 
 
ws2.Range(ws1.Cells(1,1), ws1.Cells(3,3))

 
というように引数にRangeオブジェクトを設定する場合、異なるシートだとエラーになります。
 
 
 
回答
投稿日時: 23/05/22 15:58:35
投稿者: hatena
投稿者のウェブサイトに移動

ただ、誤解を生みやすいし、なんか気持ち悪いので、自分ならシートは揃えますけどね。
 
 
回答
投稿日時: 23/05/22 15:58:46
投稿者: WinArrow

ws1.Rows.Count
と
ws2.Rows.Count
は、等しいと思うが
  
何をしようとしているか、ちょっと理解が難しいです。
 
練習問題は、

引用:
ws1のセルに日付や人名を入力すると、その条件に合うデータをws2から抜き出す

ですよね。
ws1のデータ最終行とws2データ最終行は関係ないと思いますので、
混同しないことです。
 
いきなり、コードを書く前に
要望内容を整理しましょう。
 
ws1の1件のデータを対象として、何をするのか？
 
この１件の処理が出来上がれば、
存在するデータ件数だけループする
落ちう段階に進めます。
 
 
 
回答
投稿日時: 23/05/22 16:01:35
投稿者: 半平太

＞ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row
＞これはws2の最終行を取得しているのでしょうか？それともws1の最終行を取得しているのでしょうか？
 
ws2の最終行ですよ。
 
＞何故シートを跨ぐような記述になるのかがわかりません。
分かったら、その方が不思議。
単なるケアレスミスでしょう。
 
もしくは、ほかで ws1.Rows.Countを使ったステートメントがあり、それをコピーして使った際
同じブックならどのシートでもRows.Counは同じなので、神経質に修正しなかったと言う理由も考えられます。
回答
投稿日時: 23/05/22 16:27:48
投稿者: WinArrow

推察ですが・・・
 
>For i = 2 To ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row
 
コードの記述順序から、考えると
 
ws2.Cells(
の「ws2」が記述間違いで、
最初から「ws2」側をループする必要はありませんから、
For i = 2 To ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
ではないかと思います。
 
 
回答
投稿日時: 23/05/22 22:08:50
投稿者: MMYS

最終行の取得の定石コードは
 

　Cells(最大行数,対象列).End(xlUp).Row

が定石です。なぜ上記コートなのかはExcelは最下位セルにカーソルを置き、Ctrlキーを押しながら、↑キーで、カーソルが最終行に移動します。つまり、この動きをVBAで再現したのが上記コードです。
 
ところで、Excelで扱える最大行数はExcelのバージョンで異なります。Excel2003までは約6.5万行。Eccel2007から104万行です。
・Excel2003以前　⇒　　65536行
・Eccel2007以降　⇒　1048576行
 
つまり、下記のように定数を記載するのが、正しい記述になります。
 
Excel2003以前 
　Cells(65536,1).End(xlUp).Row

Eccel2007以降は　1048576行 
　Cells(1048576,1).End(xlUp).Row

 
しかしながら、上記のように定数を書かなくても、最大行数を取得する方法があります。下記コードで、対象ワークシートで扱える最大行数が取得出来ます。 
ActivSheet.Rows.Count

ここでポイントですが、扱える最大行数は、Excelのバージョンで決まります。そして、どのワークシートでも扱える最大行数は共通です。つまり、今回のケースだと、ws1でもws2でも、1048576 が返ってきます。
 
 
今回のケースでは、1048576 の取得が目的なので、動作はします。
ただし、可読性、つまりバグを未然に防ぐ目的から、別々のワークシート指定は好ましくありません。質問者さんのように混乱しますので。おそらく、サイト作成者様は意図したコートではなく修正ミスかと思います。
 
なお、私ならこのコードは可読性優先で、下記のように記述します。
 
Dim lngLastRow   As Long '最終行
Dim lngTargetRow As Long '対象行
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("●●●")
Set ws2 = Worksheets("△△△")

lngLastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
For lngTargetRow = 2 To lngLastRow

 
回答
投稿日時: 23/05/22 22:23:08
投稿者: WinArrow

>For i = 2 To ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row
 
↑このような記述ミスを防ぐ意味を含めて
私ならば
 
   With ws1
       For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
という書き方をします。
おそらく　ループの中でも「ws1」シートの中のセルを参照するだろうから、
With ws1
で対象オブジェクトをまとめて修飾するようにします。
コードも短くなるし・・・可読性もよくなると思います。
投稿日時: 23/05/23 14:22:23
投稿者: tamtam

hatena様
WinArrow様
半平太様
MMYS様
 
返信を頂いた順に失礼します。
皆様、大変ご丁寧に教えていただきありがとうございました。
こうした記述が「修正ミスしているだけだな」とすぐに分からなかったのは勉強不足ですね。
今後も可読性や、このコードを書くことで何を実現させたいのかに留意しながら
取り組んでまいります。
また行き詰った際にはよろしくお願いいたします。