列の最終行を求めるアレはなぜわかりにくいのか(Excel)

「[Worksheet.]Cells([Worksheet.]Rows.Count, 1).End(xlUp).Row」はなぜわかりにくいのか

f:id:akashi_keirin:20210314200331j:plain

任意の列(標題の場合はA列)の下端のセルの行番号を求めるおなじみのコード。

ときどき、「なかなか覚えられねえ!」という文脈で話題になるので、ちょっと考えてみた。

なぜ「覚えられねえ」のか

これは、端的に言ってたぶん〝理解していないから〟だと思う。

人間、理解したものはなかなか忘れないものだが、理解を伴わずにただ覚えているだけだと、忘れるのも早いように思う。

そうじゃない人もいるかも知れんが。

なぜ「理解」できないのか

これは、端的に言って

[Worksheet.]Cells([Worksheet.]Rows.Count, 1).End(xlUp).Row

という〝式〟(でええんか?)が、端的に〝わかりにくい〟からであろう。

なんせ、

①シートの一番下にあるセルを取得して、②そのセルから上方向に空白でないセルに突き当たったところのセルを取得して、③そのセルの行番号を取得する

というだけでも相当ややこしいのに、さらに「①」の「シートの一番下にあるセル」を指定するために、「シートの一番下」の行番号を取得せねばならず、そのために

④シートの行の総数(つまり「「シートの一番下」の行番号」と同じ数値)を取得している

という、めちゃくちゃややこしい手順を1行で書く、という暴挙を敢えてしているからである。

可能な限り分けて書く

求める手順

〝シート上のA列の一番下の空白でないセルの行番号〟を求める手順は以下の通り。

  • A列の一番下のセル(bottomCell)を取得する
  • そのセルにカーソルを置いて、[Ctrl][ ↑ ]を押してたどり着くセル(endCell)を取得する
  • そのセルの行番号(endRow)を取得する

上記、「bottomCell」、「endCell」、「endRow」は、それぞれ後のコード内で使う変数名。

コード化

上記の手順をVBAのコード化する。

リスト1
Private Sub testEndRow()
  'A列を表す定数'
  Const COLUMN_A As Long = 1

  'アクティブシートを変数にセット'
  Dim tgtSh As Worksheet
  Set tgtSh = ActiveSheet

  '一番下のセルの行番号'
  Dim MAX_ROW_NUM As Long
  MAX_ROW_NUM = tgtSh.Rows.Count
  
  'A列の一番下のセル(bottomCell)を取得'
  Dim bottomCell As Range
  Set bottomCell = tgtSh.Cells(MAX_ROW_NUM, COLUMN_A)
  
  'bottomCellから上方に移動し、空白でないセルに突き当たったところの'
  'セル(endCell)を取得                                           '
  Dim endCell As Range
  Set endCell = bottomCell.End(xlUp)
  
  'endCellの行番号(endRow)を取得'
  Dim endRow As Long
  endRow = endCell.Row
  
  'イミディエイトに表示'
  Debug.Print endRow
End Sub

アクティブシートのA列の空白でない一番下のセルの行番号を取得して、イミディエイトに表示するだけのコード。

Dim tgtSh As Worksheet
Set tgtSh = ActiveSheet

Dim endRow As Long
endRow = tgtSh.Cells(tgtSh.Rows.Count, 1).End(xlUp).Row

と、このように書くよりは、よほど理解しやすいのではなかろうか。

おわりに

リスト1に示したように、オブジェクトや値を取得するごとに変数に格納する、というやり方で初心者向けに紹介すれば、

ただ覚えるだけのもの

ではなくなるのではないか、と思う。

参考

f:id:akashi_keirin:20210314200336j:plain

たとえば、この状態(A5セルにカーソルがある。)で、[Ctrl][ ↓ ]を押すと、

f:id:akashi_keirin:20210314200338j:plain

こうなる。

これが、〝A列の一番下のセル〟。

この状態(A1048576セルにカーソルがある。)で、[Ctrl][ ↑ ]を押すと、

f:id:akashi_keirin:20210314200342j:plain

こうなる。