真の最終行番号を取得するFunction

真の最終行番号を求めるFunction

Twitterで、

オートフィルターで非表示になっている行があると、最終行番号の取得に失敗して困る

みたいなツイを見た。

なるほど、データの入っている最終行を非表示にして、[Ctrl]+[↑]で確かめると、確かに

表示されている中で値の入っている最終行のセル

にカーソルが飛ぶ。

これは困った現象だ。

そこで、非表示になっていたとしても正確に値の入った最終行番号を返すようなFunctionを考えてみた。

考え方

非表示の行があったとしても、CurrentRegionプロパティで取得するRangeオブジェクトのRows.Countプロパティは実際の行数を返すようなので、それを利用する。

手順としては、

  1. 通常の方法(Endプロパティを使うアレ)でひとまず暫定の最終行を求める ・・・ ①
  2. 暫定の最終行にあるセルを基準にCurrentRegionを取得する
  3. CurrentRegionの最終行番号を求める ・・・②
  4. ①と②が等しければ、それが真の最終行番号
  5. ②の方が①よりも大きい場合は、②~①の間に値のあるセルがあれば、そこが最終行

こんな感じ。

この考えに基づいてコーディングしてみた。

コーディング

リスト1 標準モジュール
Public Function getLastRowNumber( _
                  ByVal targetColumn As Long, _
                  Optional ByVal targetSheet As Worksheet) As Long
  If targetSheet Is Nothing Then Set targetSheet = ActiveSheet
  Dim tmpLastRow As Long
  '暫定的な最終行を求める'
  tmpLastRow = getLastRowNumberNormal(targetColumn:=targetColumn, _
                                      targetSheet:=targetSheet)
  Dim tmpReferenceCell As Range
  Set tmpReferenceCell = targetSheet.Cells(tmpLastRow, targetColumn)
  '暫定的な最終行にあるセルのCurrentRegion最終行番号を求める'
  Dim maxRowNumber As Long
  maxRowNumber = getCurrentRegionLastRowNumber(referenceCell:=tmpReferenceCell)
  'tmpLastRowとmaxRowNumberが一致していれば、それが最終行'
  If tmpLastRow = maxRowNumber Then getLastRowNumber = tmpLastRow: Exit Function
  'maxRowNumberの方が大きい場合は、真の最終行を探す'
  Dim i As Long
  For i = maxRowNumber To tmpLastRow Step -1
    With targetSheet
      If .Cells(i, targetColumn).Value <> "" Then
        getLastRowNumber = i
        Exit Function
      End If
    End With
  Next
End Function

'指定した列の最終行番号を求める  ……(1)'
Public Function getLastRowNumberNormal( _
                  ByVal targetColumn As Long, _
                  Optional ByVal targetSheet As Worksheet) As Long
  If targetSheet Is Nothing Then Set targetSheet = ActiveSheet
  getLastRowNumberNormal = targetSheet.Cells(Rows.Count, targetColumn).End(xlUp).Row
End Function

'CurrentRegionの最終行数を取得する  ……(2)'
Public Function getCurrentRegionLastRowNumber( _
                  ByVal referenceCell As Range) As Long
  With referenceCell
    If .Count <> 1 Then Set referenceCell = .Cells(1, 1)
  End With
  Dim Sh As Worksheet
  Set Sh = referenceCell.Parent
  With referenceCell.CurrentRegion
    getCurrentRegionLastRowNumber = .Cells(.Rows.Count, 1).Row    '……(3)'
  End With
End Function
追記

上記コードでは、ある場合に真の最終行番号を返してくれません。

修正版はコチラをどうぞ。

メインは先頭のgetLastRowNumber。内部で(1)のgetLastRowNumberNormalと(2)のgetCurrentRegionLastRowNumberを呼び出すような処理にしている。

(1)、(2)とも、単独でも使い道がありそうなので、PrivateではなくPublic指定している。

(1)の

Public Function getLastRowNumberNormal( _
                  ByVal targetColumn As Long, _
                  Optional ByVal targetSheet As Worksheet) As Long
  If targetSheet Is Nothing Then Set targetSheet = ActiveSheet
  getLastRowNumberNormal = targetSheet.Cells(Rows.Count, targetColumn).End(xlUp).Row
End Function

は、おなじみEndプロパティを用いて最終行番号を求めているだけ。

(2)の

Public Function getCurrentRegionLastRowNumber( _
                  ByVal referenceCell As Range) As Long
  With referenceCell
    If .Count <> 1 Then Set referenceCell = .Cells(1, 1)
  End With
  Dim Sh As Worksheet
  Set Sh = referenceCell.Parent
  With referenceCell.CurrentRegion
    getCurrentRegionLastRowNumber = .Cells(.Rows.Count, 1).Row    '……(3)'
  End With
End Function

は、受け取ったセルのCurrentRegionの最終行番号を求めるFunction。先頭の3行

With referenceCell
  If .Count <> 1 Then Set referenceCell = .Cells(1, 1)
End With

は、今回の使い道には関係ない。単独でCurrentRegionの最終行番号を求める用途で使うときに、引数に複数セルが渡された場合の対応。

(3)の

With referenceCell.CurrentRegion
  getCurrentRegionLastRowNumber = .Cells(.Rows.Count, 1).Row
End With

がちょい分かりにくいかも。

CurrentRegionの最終行番号を求めるために、Cellsプロパティを用いてCurrentRegionの1列目最終行のセルを取得し、そのセルのRowプロパティを参照することによってCurrentRegionの最終行番号を取得している。

CurrentRegionは必ず矩形なので、1列目で良いと判断した。

メインのgetLastRowNumber内の処理は、コチラに書いたとおりなので、特に説明はいらないかな。

使ってみる

まず、シートに

f:id:akashi_keirin:20180301181245j:plain

こんな表を用意して、

f:id:akashi_keirin:20180301181255j:plain

17、18行目を非表示にして実験。

イミディエイト・ウインドウに

?getLastRowNumber(Range("P1").Column)

と入力して[Enter]!

f:id:akashi_keirin:20180301181306j:plain

ほれ、このとおり、ちゃんと真の最終行番号が返っておる。

今度は、オートフィルターでやってみる。

f:id:akashi_keirin:20180301181313j:plain

f:id:akashi_keirin:20180301181321j:plain

f:id:akashi_keirin:20180301181328j:plain

14、15行目の安道全と時遷を非表示にしてから、イミディエイト・ウインドウに

?getLastRowNumber(Range("D1").Column)

と入力して[Enter]!

f:id:akashi_keirin:20180301181335j:plain

ほれ、このとおり真の最終行番号が返った。

おわりに

ご意見承ります。

@akashi_keirin on Twitter