【OCN】1GBプレゼントCP中!

特定列に着目し、何らかのデータのある最終行を取得する場合は
Cells(Rows.Count, "A").End(xlUp).Row
ですが、「特定の」文字列あるいは数値のあるセル、複数ある場合は最後の行を知りたいのです。

詳細



え  ←ここの行番号が知りたい
#N/A
上記のようにA列に文字列あるいは数値が並んでます。その中にエラー記号である#N/A、#DIV/0!、、#VALUE!が混在します。これらエラー表示を除いた最終行、上例では「え」のある行を取得したいのです。ちなみに#N/A等はコピペ処理により文字列?になっている事もあります。

gooドクター

A 回答 (8件)

#2,5,7です。


何度もすみませんが、ふと気付けば未だどなたも
【普通に、下からループして探す】版を掲げてないようなので、
(質疑の感触としては、今回の課題にそこまでは必要ないだろうと思ってはいますが)
一例として(あくまで参考として)上げておくことにします。
無論これは[数式の戻り値]と[定数値]両方に対応させる意図で書いたものです。
(Select Case の中身は2種類からお好みで、、、)

' ' =================================

Sub Re8914431a()
Const nTop As Long = 1 ' それより上を探す必要のない上限の行を指定
Dim i As Long
  With Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) ' 必ず1行めを先頭にした1列の範囲を指定
    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

' ' =================================


以上です。お騒がせしました。
    • good
    • 0
この回答へのお礼

ありがとうございます。
これでいきます。

お礼日時:2015/02/08 13:46

WindFaller様へ


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

こんばんは。



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!
    • good
    • 0
この回答へのお礼

ありがとうございます。
UsedRangeは調べてみましたが、書式変更だけでも使ったことになるようで、使用は止めました。値だけならCells.ClearContentsの代わりに使用領域だけクリア出来ると思ったのですが。

>←空白値(Empty)※コピー&ペーストすれば、「空白文字」が混入しています。
確認してみて驚きました。数式「=""」をコピー&値のみペーストでCells(Rows.Count,"A").End(xlUp).Rowが反応します。正直、長さゼロ文字列に反応するのは仕様に問題があると思います。

お礼日時:2015/02/07 11:34

#2です。

お礼欄拝見しました。

> コピペで文字列になるというのは、例えば数式で=1/0などと書けば #DIV/0!と表示されるので、これをコピーし他セルで値のみペーストを選んで表示されるものです。あるいはvlookupで該当が無い場合の#N/Aのコピペとか。

> 無理な場合は、数式側で=IF(Iserror(vlookup), vlookup(), "")等で対処するしかありませんが、他人も使うファイルなので出来れば手を加えたくありません。

多少、誤解があるように思えるので補足しますが、、、。
セルの値を分類する場合には、
 [数式の戻り値]なのか[定数値]なのか、という分け方があります。
もう一つがデータ型での分類ですが、代表的なものに、
 [数値]※1[文字列値][論理値][エラー値]があります。
誤解されているように思われるのは、
値貼付けを含むコピペでは
[数式の戻り値]か[定数値]どうかに関わらず、
データ型が変わるようなことは基本的にありません。※2
=1/0という数式が置かれたセルの戻り値は、エラー値の#DIV/0!ですが、
これをコピーして値貼付けをしたとしても、それは、
[エラー値]であり[定数値]です。
#N/Aでも同様です。
見た目で[文字列値]のように感じてしまったとしても、
実際の値は[エラー値]です。
#VALUE!
#REF!
#DIV/0!
#N/A
のようなエラー値を示す文字列を、
セルに貼り付けたり、文字としてタイピングして入力したりしても、
そのセルの値は[エラー値]であり[定数値]です。
表計算アプリであるExcelにとっては、[エラー値]という特別な値を
扱い易くする為に(一種のショートカットとして)
#N/Aのような文字入力を可能にしている、という風に考えてください。
逆に、文字列値としての"#N/A"がセルに設定したりすると、
非常に特殊で、扱い難いファイル、として敬遠されるものだと思います。

もう一点、
ご提示の数式'=IF(Iserror(vlookup), vlookup(), "")'についてですが、
エラーだった場合の[数式の戻り値]は[文字列値]です。
この値をコピーして値貼付けをしたとしても、それは、
[文字列値]としての"長さ0の文字列"であり[定数値]です。
見た目が空白に見えても、実際には[文字列値]ですので、
例えば、range.SpecialCellsメソッド等でも、[文字列値]としてヒットすることになります。

似たような例で、
日付シリアル値としての 2015/2/6 と
文字列値としての "2015/2/6" と
見た目は同じように見えても実は別の値である場合等もあります。
このように、
見た目の[表示文字列]から想像する値と実際の[値]とが違う場合は多々ありますし、
VBAでもExcel数式でも、それらの違いをよく理解した上で扱う必要があります。

 ※1 : この場合の[数値]というのは、普通の[数値]の他に、
      [通貨][日付][時刻]等のように内部的に数値であるデータ型を含んでいます。
 ※2 : 基本的ではない特殊な例として、文字列値としての"#N/A"をセルに確定するには、
      事前にセルの[表示形式]を[文字列]に設定しておいて、
      セルを直接[入力モード]にして(数式バーなどに)#N/Aを入力することで
      可能ではあります。が、普通はこんなことしない方がいいです。

補足としては以上になりますが、
エラー値だけを振るい落とす、という要求であったなら、
range.SpecialCellsメソッドを用いた#2の方法で十分だと考えています。

以下、蛇足ですが、
#3様の、
> SepecialCells というのは、独特の使い方があるので、実験的には可能でも、実務的にはややこしくなるはずです。
どのような状況のことを仰るのか、私には理解(想定)できていません。
具体的に書いて貰えれば、こちらも勉強になるのになぁ、
という思いで話に触れました。他意はまったくありません。
    • good
    • 0
この回答へのお礼

再度ありがとうございます。
確認してみました。確かに関数式の戻り値としての#DIV/0!と自分でタイプした#DIV/0!の両方をIserror()にかけたところ、どちらもTRUEでした。どちらでも使えるようで一安心です。

また、""が文字列扱いとは知りませんでした。。。。って書いてて気づきましたが、そもそもダブルクォーテーション使った時点で文字列ですよね。

お礼日時:2015/02/07 00:50

No.1です。



>エラーを除いて残った数値の中で最終行を知りたいのです

というコトはNo.2さんやNo.3さんの回答通り
データの最終行から地道にループさせ、エラーセルが見つかれば
それより前のセルでエラーもしくは空白以外のセルが見つかるまでループさせるのが
一番手っ取り早いと思います。m(_ _)m
    • good
    • 0
この回答へのお礼

再度ありがとうございます。

お礼日時:2015/02/07 00:28

こんにちは。



>#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
'//
    • good
    • 0
この回答へのお礼

ありがとうございます。
上から順に調べる方法ですね。Is関連は関数式のみ使っており、VBAで使うことが無かったので、試してみます。

お礼日時:2015/02/07 00:26

こんにちは。



 指定した列範囲内で、
 エラー値以外の定数値を持つセル範囲の
 一番したにある行
ということに対して、まっすぐに取り組む
(最下行がエラー値以外の定数である場合に対応させる)
なら、以下のように書くことが出来ます。

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

 A列の
  定数値 且 (文字列値 または 数値 または 論理値)
  であるセル範囲を取得。
 この段階で、飛び飛びの複数セル範囲が取得されていとして、
  r.Areas(r.Areas.Count)で、最後のブロック(矩形範囲)を取得し
   .Cells(.Cells.Count)でブロック内の最後のセルを取得
といった流れになります。

> ちなみに#N/A等はコピペ処理により文字列?になっている事もあります。
とりあえず、
 #VALUE!
 #REF!
 #DIV/0!
 #N/A
を試しに色んな手順でコピペしてみましたが、'文字列?'になる、というのは、
どういうことなのか(どういう手順で再現できるのか)判りませんでした。
何であれ、エラー値に加え'文字列?'をも除く必要がある、ということでしたらば、
端から普通に、下からループして探すのが妥当になるのではないかと思いますが、、、。
'文字列?'の由来が判れば、また違うトライも可能なのかも知れません?

取り敢えず、以上です。
    • good
    • 0
この回答へのお礼

ありがとうございます。
SpecialCellsを使って特定種類のセルを選べるようですね。是非使ってみます。コピペで文字列になるというのは、例えば数式で=1/0などと書けば #DIV/0!と表示されるので、これをコピーし他セルで値のみペーストを選んで表示されるものです。あるいはvlookupで該当が無い場合の#N/Aのコピペとか。

無理な場合は、数式側で=IF(Iserror(vlookup), vlookup(), "")等で対処するしかありませんが、他人も使うファイルなので出来れば手を加えたくありません。

お礼日時:2015/02/06 20:32

こんばんは!



>上例では「え」のある行を取得したいのです

エラーのすぐ上のセルを選択するのではなく、エラーのすぐ上の行番号を取得したい!
という解釈ですの一例です。
エラーとは数式によるエラーとします。

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
    • good
    • 0
この回答へのお礼

ありがとうございます。
エラーの上と書いてしまし誤解を招きました。すみません。エラーを除いて残った数値の中で最終行を知りたいのです。

お礼日時:2015/02/06 20:19

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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

gooドクター

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

人気Q&Aランキング