エクセルVBAで最終行取得ができない

質問

エクセルVBAでたとえばC列にどこまでデータが入力されているかを調べるとき、通常は
r = Cells(Rows.Count, "C").End(xlUp).Row
などでできます。
しかし添付画像のような入力フォームが出来上がっており、かつC列に最初から何らか（画像では〒マーク）の入力がされているので、そこに回答者が途中まで入力された場合、これでは最終行は取得できません。
逆に上からEnd(xlDown)でやろうとしても、途中に空白セルがあってお手上げです。
余分な〒マークを元データから削除してしまいたいのですがそれでは入力されているところのマークまで消えてしまいます。
このようなファイルが何百もあり、それを1枚のシートにまとめようとしているのですが、この最終行取得でつまずいてしまいました。
どのような方法があるでしょうか？
画像はエクセル2013ですが、実際にマクロを動かすのは2010です。

回答No.6

real beatin（@realbeatin） · ベストアンサー

＃５です。お礼欄、補足欄、拝見しました。

> いいえ、名前がなく、他のデータがあるレコードは、不備データ「名前入力漏れ」として再回答を依頼することになります。
了解しました。

> 入力された最終行から有意なレコードの内の一番下にあるデータセットの最終データ[備考２]の行位置を求めるため、MOD関数を使い以下のようにしてみましたが、これ正しいでしょうか？
問題なく、ばっちり正しい結果が返せています。
　　If (nBtmRow - 1) Mod 6 > 0 Then
　　　　x = nBtmRow + 6 - (nBtmRow - 1) Mod 6
　　Else
　　　　x = nBtmRow
　　End If
のように、括弧がひとつ減らして書くことも出来ます。
　「Visual Basic における演算子の優先順位」
　https://msdn.microsoft.com/ja-jp/library/fw84t893(v=vs.80).aspx
　こちらで補足すると、
　「Mod 演算子」は「加算と減算 (+、–) 演算子」より優先で計算され、
　「乗算と除算 (*, /) 演算子」は「Mod 演算子」より優先で計算されます。
また私なら、
　　x = 6 * ((nBtmRow + 4) \ 6) + 1
のように条件分岐なしの１行で済ませる書き方をしたり、
またはレコード数を先に求めるようにして
　　cnt = (nBtmRow + 4) \ 6
　　x = 6 * (cnt) + 1
のように（必要なくてもレコード数を明示して）書いて残して、
特に説明を添えなくても、
他人にも（忘れた頃の自分にも）理解され易く
メンテが楽になる方向で書き方を選ぶことが多いです。
ここら辺は人それぞれ好みの分かれる処かも知れませんね。

お礼欄、補足欄、への返信、以上です。

お礼

ありがとうございました！
x = 6 * ((nBtmRow + 4) \ 6) + 1
なんでこれ１行で済ませられるのが悩みました。
レコード数とちゃんと念押しで書いていただいたのでやっと理解できたみたいです。
4は、項目数と各レコードの最初の行の行数の差なんですね？
だから取得した最終行番号に4を加え、項目数の6で割った値の整数値が入力されたレコード数になる。
したがってレコード数と項目数の積に見出し行の+1で最終データ[備考２]の行位置が求められたということなんですね？
すぐにこんなのがわかっちゃうrealbeatinさんはすごいです。
わたしは鈍いですねえ・・・。

回答No.8

real beatin（@realbeatin）

では、一応、Evaluate版も上げときますね。
でも、これ、誰がメンテするのか、ってことがネックになると思います。
実際ここでの質問でもEvaluateネタにはなかなか回答付かないですし、、、。
（本当はEvaluateの戻り値エラーに対する処理も必要ですが省略）

Sub Re8939737ev()
Const S_FML = "max(row(C1:C#)*(C1:C#<>"""")*(C1:C#<>""〒""))"
Dim nBtmRow As Long, cnt As Long, vRtn
　　With ActiveSheet
　　　　nBtmRow = .Cells(Rows.Count, "C").End(xlUp).Row
　　　　vRtn = .Evaluate(Replace(S_FML, "#", nBtmRow))
　　End With
　　cnt = (vRtn + 4) \ 6
　　nBtmRow = 6 * cnt + 1
　　MsgBox nBtmRow
End Sub

お礼

realbeatin さん、今回はいろいろありがとうございました。
Evaluateって聞いたことがなく、何か難しいような気がします。
いまは、明日までにこの質問に書いた何百ファイルの統合に取り組んでますので、後日勉強させていただきたいと思います。
実はその前に、また問題が起きてしまったのです。
ひとつは、シート保護されているファイルがいくつかあり、オートフィルタが使えない！
もうひとつはセル内の文字数が多すぎるのか、取得したデータを
Application.Transposeで、別BOOKに横向きに転記しようとすると、エラーになる！
解決できない場合、再度新たな質問をたてるかもしれません。
今回はありがとうございました。

回答No.7

Nouble

VBA用関数しか使えない
VBA用関数しか使ってはいけない
では、無いですよね？

Evaluateでシート関数を使えば
意図も簡単にできます

ただ、
Evaluateは扱いが難しいので
其の点が、難…
ですが

MIN(INDEX((Left(A:A,2)="No")*ROW(A:A)+(Left(A:A,2)<>"No")*999999999999999,,))

此を、
Evaluateに改造すれば
行ける…
で、しょう（？）

コツは、
定数文字列は
変数に入れてしまえ
さらば楽

ですね

手元にエクセルが無いので
何ともいい難い
のですが…

Option　Explicit
'
Function　最終項探査(探査キー　Az　String)　Az　Long
'
　　最終項探査　=　Evaluate("MIN(INDEX((Left("A:A",2)="探査キー")*ROW("A:A")+(Left("A:A",2)<>"探査キー")*999999999999999,,))")

Sub　呼び出しはこんな雰囲気()
　Range("C"&最終項探査("No")).Select
End　sub

バグっていたらご免なさい

Evaluate解説ページ
www.ne.jp/asahi/hishidama/home/tech/excel/workfunc.html

お礼

早朝の回答、ありがとうございます。

> Evaluateでシート関数を使えば

初めて聞く言葉で、面食らってます。
いまは、明日までの課題（この質問に書いた何百ファイルの統合）に取り組んでおりますので、後日勉強させていただきます。
ありがとうございました。

回答No.5

real beatin（@realbeatin）

こんにちは。お邪魔します。

専ら実務ベースの応用的な回答になります。
「〒マーク」のことは一旦忘れてください。

　[名前]
　[住所]
　[電話]
　[メアド]
　[備考１]
　[備考２]
という６項目単位のデータが一括りになっていて、
有意なデータであれば、６項目の内のひとつでも空欄があったとしても、
「空欄であるという情報」までは、消さない意図なのだと思います。
（それを消すぐらいならrange.SpecialCells (xlCellTypeBlanks)や
　転記方法についての言及がある筈ですから、、、）
今回の課題には書かれていなくても、
その後の処理の過程では、６項目単位で処理されるでしょうから、
有意なレコードの内の一番下にあるデータセットの
最終データ[備考２]の行位置を求めることになるのでしょう。
（∵> このようなファイルが何百もあり、それを1枚のシートにまとめようとしている...）

> そこに回答者が途中まで入力された場合
どこであれ「途中まで入力された場合」を想定するということになりますから、
　６項目の内、[名前]が入力されていれば、有意なデータ。
　６項目の内、[名前]以外の項目だけ入力されているのは無効なデータ。
のように切り捨てていくのが、現実的な対応と思います。
名前（ＩＤ）の無いレコードは扱いようがない訳ですし、
逆に、名前（ＩＤ）が入力済であればこそ、「途中まで入力された」
という判断が付くのではないでしょうか。
これは、プログラムの中身としてのロジックのことではなくて、
実務レベルでの設計基準という視点になりますから、
他の考え方があったとしても、それは職務に副った要求、
ということになるのだと思いますから、
もし違っているなら、別の方法を考えないとなりませんが、
以上の説明は、ひとつの解釈としては成立していると考えますので、
その方法を示します。

　Ｂ列に対して、オートフィルターで[名前]項目だけを抽出します。
　Ｃ列に対して、.End(xlUp).Rowを実行し、[名前]が入力されている最下行を取得。
　最下の[名前]行の５行下の[備考２]行を取得する。

' ' ///
Dim nBtmRow As Long
　　With ActiveSheet
　　　　.AutoFilterMode = False
　　　　.Columns("B").AutoFilter Field:=1, Criteria1:="=名前"
　　　　nBtmRow = .Cells(Rows.Count, "C").End(xlUp).Row + 5
　　　　.AutoFilterMode = False
　　End With
' ' ///

> このようなファイルが何百もあり ...
というお話ですから、
きっとループの中で、Workbook、Worksheet、をオブジェクトとして
順次捉えながら処理することになるのかと思いますが、
例示では仮にActiveSheetを対象としていますので、適宜書換えてください。
（このやり方では、常に掛けっ放しのオートフィルターがあっても
　キャンセルされます。必要なら手当ては可能です。）

何か不足があれば、補足をお願いします。

お礼

> その後の処理の過程では、６項目単位で処理されるでしょうから、
> 有意なレコードの内の一番下にあるデータセットの
> 最終データ[備考２]の行位置を求めることになるのでしょう。

まさにその通りです。

> 名前（ＩＤ）の無いレコードは扱いようがない訳ですし、

いいえ、名前がなく、他のデータがあるレコードは、不備データ「名前入力漏れ」として再回答を依頼することになります。
ありがとうございました。

補足

入力された最終行から有意なレコードの内の一番下にあるデータセットの最終データ[備考２]の行位置を求めるため、MOD関数を使い以下のようにしてみましたが、これ正しいでしょうか？

Sub test02()
Dim nBtmRow As Long
With ActiveSheet
.AutoFilterMode = False
.Columns("C").AutoFilter Field:=1, Criteria1:="<>〒"
nBtmRow = .Cells(Rows.Count, "C").End(xlUp).Row
.AutoFilterMode = False
End With
If (nBtmRow - 1) Mod 6 > 0 Then
x = nBtmRow + 6 - ((nBtmRow - 1) Mod 6)
Else
x = nBtmRow
End If
MsgBox x
End Sub

回答No.4

kagakusuki

その様な場合はオートフィルターを使用して「『"〒"とだけ入力されている行』以外の行」のみを表示させてから

r = Cells(Rows.Count, "C").End(xlUp).Row

という処理を行う様なVBAにすると良いと思います。

Sub ()

Dim r As Long '変数rを定義
Columns("$C").AutoFilter Field:=1, Criteria1:="<>〒" '「〒」と入力されていない行のみを表示
r = Cells(Rows.Count, "C").End(xlUp).Row '最終行の行番号取得
Columns("$C").AutoFilter 'オートフィルターモードの解除

End Sub

お礼

なんと、オートフィルタで非表示になった行はEnd(xlUp)でひっかからないのですね！
これは知りませんでした。
とても役に立つご教示をありがとうございました。

回答No.3

matsu_jun

スマホからの回答ですので、細かなルーチンまでの記載はできないことはご勘弁を。

大きく分けると三通りのやり方があると思います。

1) 下からxlupで検索し、結果行のセルの値が「〒」だった場合は、Rows.Countの代わりに、(結果行-1)を入れて検索し直してやればokです。Do While構文で回しましょう。

2) 表を見たところ、二行目から六行おきにデータが入っています。名前の記載が必須だとすると、まずは二行目のセルが空欄かを確認、空白でなければ次は8行目、つど行数に6を足しながら、空欄を確認するまでDo While文で回しましょう。

3) 一時的に作業列を作ります。例えばZ列に、〒を消す数式(replaceかrightで一文字だけ消すか)をしてその列に対してEndプロパティをかけてやれば良いかと。

特に上の二つは、データ量によってはかなり時間がかかるかもしれません。

お礼

ありがとうございました。

回答No.2

FEX2053

もっとプリミティブに。

Range("B65535").end(xlUp).Row

これで最終行番号が得られるはずです。

すなわち、「最終行までセルにデータが埋まってる列を使って、
最終行より明らかに先のセルから、[ctrl]+[↑]を操作する」
と言う操作のマクロ記録で行ける、って話です。

セル選択は何もCellsにこだわる必要なんてないですから。

あと、どうしてもC列でチェックしなくちゃいけない場合は、
「その行は必ずデータが入っているはず」の行を順番に

For I=1 to 65536 Step 10
if Cells(I,3)="" then Exit For
Next

みたいな形で、Iをチェックする方法もあります。

お礼

ありがとうございあす。
Range("B65535").end(xlUp).Rowでは、あらかじめ作成された入力用フォームの最終行になり、実際に入力してもらうC劣の最終行ではありません。

>「その行は必ずデータが入っているはず」の行を順番

これはいいですね、ありがとうございました。

回答No.1

kuma56

>かつC列に最初から何らか（画像では〒マーク）の入力がされているので・・・・
そのなんらかがさいしょからわかっているなら、
r = Cells(Rows.Count, "C").End(xlUp).Row
のあとに、IF関数でセルの内容を判断して、該当する物なら消去して、もう一度最終行は取得しにいけば？？

お礼

ありがとうございます。
たしかにその通りなのですが、あまりに手間取りすぎ、何百ファイルをそれでまわすと時間がかかりそうです。