真の最終行番号を取得するFunction
真の最終行番号を求めるFunction
Twitterで、
オートフィルターで非表示になっている行があると、最終行番号の取得に失敗して困る
みたいなツイを見た。
なるほど、データの入っている最終行を非表示にして、[Ctrl]+[↑]で確かめると、確かに
表示されている中で値の入っている最終行のセル
にカーソルが飛ぶ。
これは困った現象だ。
そこで、非表示になっていたとしても正確に値の入った最終行番号を返すようなFunctionを考えてみた。
考え方
非表示の行があったとしても、CurrentRegionプロパティで取得するRangeオブジェクトのRows.Countプロパティは実際の行数を返すようなので、それを利用する。
手順としては、
- 通常の方法(Endプロパティを使うアレ)でひとまず暫定の最終行を求める ・・・ ①
- 暫定の最終行にあるセルを基準にCurrentRegionを取得する
- CurrentRegionの最終行番号を求める ・・・②
- ①と②が等しければ、それが真の最終行番号
- ②の方が①よりも大きい場合は、②~①の間に値のあるセルがあれば、そこが最終行
こんな感じ。
この考えに基づいてコーディングしてみた。
コーディング
リスト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内の処理は、コチラに書いたとおりなので、特に説明はいらないかな。
使ってみる
まず、シートに
こんな表を用意して、
17、18行目を非表示にして実験。
イミディエイト・ウインドウに
?getLastRowNumber(Range("P1").Column)
と入力して[Enter]!
ほれ、このとおり、ちゃんと真の最終行番号が返っておる。
今度は、オートフィルターでやってみる。
14、15行目の安道全と時遷を非表示にしてから、イミディエイト・ウインドウに
?getLastRowNumber(Range("D1").Column)
と入力して[Enter]!
ほれ、このとおり真の最終行番号が返った。
おわりに
ご意見承ります。