VBA データ（特定値）のある最終行を取得したい

Question

特定列に着目し、何らかのデータのある最終行を取得する場合は
Cells(Rows.Count, "A").End(xlUp).Row
ですが、「特定の」文字列あるいは数値のあるセル、複数ある場合は最後の行を知りたいのです。

詳細
あ
え
う
え  　←ここの行番号が知りたい
#N/A
上記のようにA列に文字列あるいは数値が並んでます。その中にエラー記号である#N/A、#DIV/0!、、#VALUE!が混在します。これらエラー表示を除いた最終行、上例では「え」のある行を取得したいのです。ちなみに#N/A等はコピペ処理により文字列？になっている事もあります。

cj_mover · Accepted Answer

＃２,５,７です。
何度もすみませんが、ふと気付けば未だどなたも
【普通に、下からループして探す】版を掲げてないようなので、
（質疑の感触としては、今回の課題にそこまでは必要ないだろうと思ってはいますが）
一例として（あくまで参考として）上げておくことにします。
無論これは[数式の戻り値]と[定数値]両方に対応させる意図で書いたものです。
（Select Case の中身は２種類からお好みで、、、）

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub Re8914431a()
Const nTop As Long = 1　'　それより上を探す必要のない上限の行を指定
Dim i As Long
　　With Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)　'　必ず１行めを先頭にした１列の範囲を指定
　　　　For i = .Count To nTop Step -1
　　　　　　Select Case VarType(.Cells(i))
　　　　　　Case vbEmpty, vbError　'　vbEmpty=0,vbError=10　'　op#1/2
　　　　　　Case Else　'　op#1/2
　　　　　　　　Exit For　'　op#1/2
　　　　　　End Select　'　op#1/2
　　　　Next i
　　End With
　　If i < nTop Then i = nTop
　　MsgBox i
End Sub

''　　　　　　Case 5 To 8, 11　'　vbDouble=5,vbCurrency=6,vbDate=7,vbString=8,vbBoolean=11　'　op#2/2
''　　　　　　　　Exit For　'　op#2/2
''　　　　　　End Select　'　op#2/2

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝


以上です。お騒がせしました。

cj_mover · Answer

WindFaller様へ
ありがとうございました。
御返事頂いた＃６の内容に関しては私にも十分理解できるものでした。
＃６のコードも読み込みましたが、私個人としてはとても好きな書き方でした。
＃２では決め打ち型の回答を意図していた私でしたが、少し説明が足りなかったかも知れませんね。
エラートラップを省略した理由や、[定数値]に限定した記述であることの
説明ぐらいは書いておくべきだったと、今になって思っています。
なるべく汎用的で、言わば性悪説的で卒のないものを提示したい、
という回答スタイルを、以前は（出来るかは別で）目指していましたが、
最近は、なるべく要点を絞って
伝わり易い（自身で出来る理解に近づかせてあげられる）回答を心掛けよう、
という風に私の考えも少し変わってきました（まだまだ出来てないんでしょうけれど）。
そういう意味で尺度の持ち様は色々ある、ということも踏まえた上で、
優しくお答えいただいたものだと受け止めましたので、
尚ありがたく存じます。感謝しております。
今後ともよろしくお願いします。　　ｃｊ　拝

WindFaller · Answer

こんばんは。

High_Score様へ
別所で書きました、UsedRangeそのままでは、最終行は、確実には取れません。
UsedRangeは、使用している範囲ですから、必ず、A1からだとは限りません。逆に、A1にダミーでも置かないといけません。また、A列目にUsedRangeが掛かっても、他の場所のセルが最終行にあれば、
A列目は空白を指しています。したがって、UsedRangeではうまくありません。

#5のcj_mover様のご質問ですが、
>> SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなる>はずです。
>どのような状況のことを仰るのか、私には理解（想定）できていません。

昨日、SpecialCellsで答えを導き出そうとしていましたが、出来があまり良くなかったというか、スッキリした内容にならないのでした。今回は設問には出てきていない例ですが、[定数・数式]混在で、1列に飛び飛びの値が散在してい場合です。

プログラミングとしては、本来、常識的に考える自体を想定して作るものだと思います。
SpecialCellsは、本来、目視で確認して、その該当するものを、[定数と数式]で分けて扱う、ワークシート用の組み込みマクロだといっても良いかと思います。

いろいろな想定の中で、[定数・数式]の片方がなければ、SpecialCells は、実行時エラーをはきます。むろん、[定数・数式]混在している場合には、SpecialCellsは、二つ使わないといけません。それぞれが、エラーを出すことを想定して作らなければならないし、仮に、[定数・数式]の両方のオブジェクトが存在していて、それらを、Unionで統合したとしても、今度は、そのAreasの順番が、セル位置とは順序どおりにならないのでは、とてもややこしくなってしまいます。

あえて、昨日作ったお粗末な内容を書きますが、Unionで統合することはやめ、以下のように比較するだけにしました。しかし、このようなコードを誰が評価するか、と思うのです。

それで、結局、昨日、4つ作った中で、cj_moverさんがご指摘になった、ループで探していく方法に落ち着きました。

'//出せなかったサンプル例
Sub TestSample2()
　Dim c1 As Range
　Dim c2 As Range
　Dim Rng As Range
　Dim Ret As Variant
　Dim i As Long
　Dim a, b
　On Error Resume Next　'←エラートラップ
　Set c1 = Columns("A").SpecialCells(xlCellTypeFormulas, 7)
　Set c2 = Columns("A").SpecialCells(xlCellTypeConstants, 7)
　On Error GoTo 0
　 If c1 Is Nothing And c2 Is Nothing Then
　　　　 Ret = "N/A"
　 ElseIf Not c1 Is Nothing Or Not c2 Is Nothing Then
　　　 If Not c1 Is Nothing Then
　　　　 a = LastRowFind(c1)
　　　 End If
　　　 If Not c2 Is Nothing Then
　　　　 b = LastRowFind(c2)
　　　 End If
　　　 Ret = IIf(a >= b, a, b)
　 End If
　MsgBox "Last Row: " & Ret
End Sub

Function LastRowFind(r As Range)
Dim Ret As Variant
Dim i As Long
　　 For i = 1 To r.Areas.Count
　　　With r.Areas(i)
　　　 Ret = .Cells(.Cells.Count).Row
　　　End With
　　 Next
　LastRowFind = Ret
End Function
'//
***********
シート上の見本データ
A列
--
あ
い
　　　←空白値(Empty)※コピー＆ペーストすれば、「空白文字」が混入しています。
え
お
か
き
　　←空白値(Empty)
　　←空白値(Empty)
#N/A
#DIV/0!
#VALUE!

cj_mover · Answer

＃２です。お礼欄拝見しました。

> コピペで文字列になるというのは、例えば数式で=1/0などと書けば　#DIV/0!と表示されるので、これをコピーし他セルで値のみペーストを選んで表示されるものです。あるいはvlookupで該当が無い場合の#N/Aのコピペとか。

> 無理な場合は、数式側で=IF(Iserror(vlookup), vlookup(), "")等で対処するしかありませんが、他人も使うファイルなので出来れば手を加えたくありません。

多少、誤解があるように思えるので補足しますが、、、。
セルの値を分類する場合には、
　[数式の戻り値]なのか[定数値]なのか、という分け方があります。
もう一つがデータ型での分類ですが、代表的なものに、
　[数値]※１[文字列値][論理値][エラー値]があります。
誤解されているように思われるのは、
値貼付けを含むコピペでは
[数式の戻り値]か[定数値]どうかに関わらず、
データ型が変わるようなことは基本的にありません。※２
=1/0という数式が置かれたセルの戻り値は、エラー値の#DIV/0!ですが、
これをコピーして値貼付けをしたとしても、それは、
[エラー値]であり[定数値]です。
#N/Aでも同様です。
見た目で[文字列値]のように感じてしまったとしても、
実際の値は[エラー値]です。
#VALUE!
#REF!
#DIV/0!
#N/A
のようなエラー値を示す文字列を、
セルに貼り付けたり、文字としてタイピングして入力したりしても、
そのセルの値は[エラー値]であり[定数値]です。
表計算アプリであるExcelにとっては、[エラー値]という特別な値を
扱い易くする為に（一種のショートカットとして）
#N/Aのような文字入力を可能にしている、という風に考えてください。
逆に、文字列値としての"#N/A"がセルに設定したりすると、
非常に特殊で、扱い難いファイル、として敬遠されるものだと思います。

もう一点、
ご提示の数式'=IF(Iserror(vlookup), vlookup(), "")'についてですが、
エラーだった場合の[数式の戻り値]は[文字列値]です。
この値をコピーして値貼付けをしたとしても、それは、
[文字列値]としての"長さ０の文字列"であり[定数値]です。
見た目が空白に見えても、実際には[文字列値]ですので、
例えば、range.SpecialCellsメソッド等でも、[文字列値]としてヒットすることになります。

似たような例で、
日付シリアル値としての 2015/2/6 と
文字列値としての "2015/2/6" と
見た目は同じように見えても実は別の値である場合等もあります。
このように、
見た目の[表示文字列]から想像する値と実際の[値]とが違う場合は多々ありますし、
ＶＢＡでもExcel数式でも、それらの違いをよく理解した上で扱う必要があります。

　※１　：　この場合の[数値]というのは、普通の[数値]の他に、
　　　　　　[通貨][日付][時刻]等のように内部的に数値であるデータ型を含んでいます。
　※２　：　基本的ではない特殊な例として、文字列値としての"#N/A"をセルに確定するには、
　　　　　　事前にセルの[表示形式]を[文字列]に設定しておいて、
　　　　　　セルを直接[入力モード]にして（数式バーなどに）#N/Aを入力することで
　　　　　　可能ではあります。が、普通はこんなことしない方がいいです。

補足としては以上になりますが、
エラー値だけを振るい落とす、という要求であったなら、
range.SpecialCellsメソッドを用いた＃２の方法で十分だと考えています。

以下、蛇足ですが、
＃３様の、
> SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなるはずです。
どのような状況のことを仰るのか、私には理解（想定）できていません。
具体的に書いて貰えれば、こちらも勉強になるのになぁ、
という思いで話に触れました。他意はまったくありません。

tom04 · Answer

No.1です。

＞エラーを除いて残った数値の中で最終行を知りたいのです

というコトはNo.2さんやNo.3さんの回答通り
データの最終行から地道にループさせ、エラーセルが見つかれば
それより前のセルでエラーもしくは空白以外のセルが見つかるまでループさせるのが
一番手っ取り早いと思います。m(_ _)m

WindFaller · Answer

こんにちは。

>#N/A等はコピペ処理により文字列？
これは、テキスト値では、文字列ですから、コピー＆ペーストしたら、定数のエラーとして反応してしまいます。

SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなるはずです。確かにご質問の通りのエラーが入っている条件なら問題ないはずです。しかし、いろんな場合を考えて、昨日から試してみたけれども、3つの方法を思いつきましたが、その内、一番確実なものを出しておきます。

#2様のおっしゃった通り、
>下からループして探すのが妥当になるのではないかと思いますが

昨日、実験的に考えたのは、以下の通りです。
---
あ
え
う
え　　←ここの行番号が知りたい
　　　←が空白値の場合
#N/A

これは、定数・数式に関わりなく、エラー値を除く、データの最後【空白行の手前】ということになりますから、5ではなく、「4」という答えになります。

それと、#2様がご提示されたものに、手を加えた
エラー
エラー
エラー
あ
エラー
エラー

この場合は、4にデータがあっても、それ以下にエラー値があるので、意味をなしません。
'//
Sub TestSample4()
　Dim rw As Long, i As Long
　Dim ErrRow As Long
　Dim r As Range
　Dim LastRow As Long
　rw = Cells(Rows.Count, 1).End(xlUp).Row
　ErrRow = rw
　If rw = 1 Then MsgBox "Blank Data": Exit Sub
　Set r = Range("A1", Cells(rw, 1))
　For i = 1 To rw
　　If Not (IsError(Cells(i, 1)) Or IsEmpty(Cells(i, 1))) Then　'エラーと空白を除く
　　　LastRow = Cells(i, 1).Row
　　ElseIf IsError(Cells(i, 1)) Then
　　　If LastRow = 0 Then ErrRow = Cells(i, 1).Row 'エラーのセルを調べる
　　End If
　Next i
　If ErrRow < LastRow Then
　　 MsgBox "This Row is not available!", 48
　Else
　　 MsgBox "LastRow: " & LastRow, 64
　End If
End Sub
'//

cj_mover · Answer

こんにちは。

　指定した列範囲内で、
　エラー値以外の定数値を持つセル範囲の
　一番したにある行
ということに対して、まっすぐに取り組む
（最下行がエラー値以外の定数である場合に対応させる）
なら、以下のように書くことが出来ます。

Sub Re8914431()
Dim r As Range
　　Set r = Range("A:A").SpecialCells(Type:=xlCellTypeConstants, Value:=xlTextValues Or xlNumbers Or xlLogical)
　　If r Is Nothing Then
　　　　MsgBox "エラー値以外の定数値は存在しません"
　　Else
　　　　With r.Areas(r.Areas.Count)
　　　　　　MsgBox .Cells(.Cells.Count).Row
　　　　End With
　　End If
End Sub

　Ａ列の
　　定数値　且　（文字列値　または　数値　または　論理値）
　　であるセル範囲を取得。
　この段階で、飛び飛びの複数セル範囲が取得されていとして、
　　r.Areas(r.Areas.Count)で、最後のブロック（矩形範囲）を取得し
　　　.Cells(.Cells.Count)でブロック内の最後のセルを取得
といった流れになります。

> ちなみに#N/A等はコピペ処理により文字列？になっている事もあります。
とりあえず、
　#VALUE!
　#REF!
　#DIV/0!
　#N/A
を試しに色んな手順でコピペしてみましたが、'文字列？'になる、というのは、
どういうことなのか（どういう手順で再現できるのか）判りませんでした。
何であれ、エラー値に加え'文字列？'をも除く必要がある、ということでしたらば、
端から普通に、下からループして探すのが妥当になるのではないかと思いますが、、、。
'文字列？'の由来が判れば、また違うトライも可能なのかも知れません？

取り敢えず、以上です。

tom04 · Answer

こんばんは！

＞上例では「え」のある行を取得したいのです

エラーのすぐ上のセルを選択するのではなく、エラーのすぐ上の行番号を取得したい！
という解釈ですの一例です。
エラーとは数式によるエラーとします。

Sub Sample1()
    Dim c As Range
    Set c = Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors)
    If Not c Is Nothing Then
        MsgBox c(1).Row - 1
    End If
End Sub

※　エラーのすぐ上のセルを選択したい場合は
＞MsgBox c(1).Row - 1
を
＞c(1).Offset(-1).Select
にしてください。m(_ _)m

VBA データ（特定値）のある最終行を取得したい

＃２,５,７です。

WindFaller様へ

こんばんは。

＃２です。

No.1です。

こんにちは。

こんにちは。

こんばんは！

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング