excel2000で特定セルから空セルの前までの行数を数えたい

質問

セルＡ７から下へ向かってデータが入っています。その数をセルＡ１に入れたいと思い、

　　「　Cells(1, 1) = Range("a7", Range("a7").End(xlDown)).Rows.Count　」

こんなマクロを書きました。出展は
　「　http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+200611/06110262.txt　」です。

ここで、下記のようなデータを入れて試してみました。（）内が期待する値で、[]内が実際の値です。

　　　　　　　＜テスト１＞　＜テスト２＞　＜テスト３＞　＜テスト４＞　＜テスト５＞
セルＡ７　　　　　　　　　　　あああ　　　　あああ　　　　　　　　　　　あああ　
セルＡ８　　　　　　　　　　　　　　　　　　いいい　　　　いいい　　　　いいい　
セルＡ９　　　　　　　　　　　　　　　　　　　　　　　　　ううう　　　　　　　　
セルＡ１０　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
セルＡ１１　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　おおお
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
期待する値　　　　　０　　　　　　１　　　　　　２　　　　　　０　　　　　　２
実際の値　　６５５３０　　６５５３０　　　　　　２　　　　　　２　　　　　　２

と、このように期待する値が得られません。何が間違っているのでしょうか。
丸１日考えているのですが原因が分かりません。よろしくお願いします。
（文字幅がおかしくて見にくいと思いますがよろしくお願いします）

回答No.3

Wendy02 · ベストアンサー

こんにちは。

正しいとか間違っているというよりも、「期待する値」に、特別な法則を持っている、と思います。

Cells(1, 1) = Range("a7", Range("a7").End(xlDown)).Rows.Count
End プロパティは、値のあるセルを下に向かって探せ、ということです。だから、A7 から、その下を最後まで探して、その行数を出せ、ということでは、意味が違いますね。

質問者さんの直感なのでしょうか、「期待する値」の求め方は、関数タイプですね。「最初の空白行を探せ」ということですね。

関数なら、こうなります。

=MATCH(TRUE,INDEX(ISBLANK(A7:A100),,),0)-1

7行目より手前か、引数の範囲の外に数式を置いてください。循環参照になります。
もちろん、この数式をマクロに代入しても構いませんが、少し工夫が必要です。

以下のマクロと比較すると、上記の数式に軍配が上がりそうですね。Excelのワークシートはマクロよりも難しいですね。

'------------------------------------

Sub Test1()
　　Dim i As Integer
　　Dim ret As Long
　　Const col As Integer = 5
　　'書き出す行...15 行目
　　Cells(15, col).Resize(, 5).ClearContents
　　For i = 1 To col
　　　　ret = spBlankRow(Cells(7, i))　'7行目から
　　　　Cells(15, i).Value = ret - 7
　　Next
End Sub
Function spBlankRow(rng As Range) As Long
Dim r As Range
　 On Error Resume Next
　 Set r = Range(rng, Cells(Rows.Count, rng.Column)).SpecialCells(xlCellTypeBlanks)
　 On Error GoTo 0
　 spBlankRow = r.Cells.Cells(1).Row
End Function

ちょっと言い訳：
最初、ユーザー定義関数だけで作ろうと思いましたが、SpecialCells で範囲を認識させるには、コマンドに命令を与える信号が必要ですが、ユーザー定義だけでは、そのコマンドに命令が与えられないので、プロシージャ型になっています。

回答No.4

Wendy02

#3の回答者です。

「#1 のお礼」に対するレス

Range("A7").End(xlDown) というのは、

A7にカーソルを置いて、Ctrl + ↓　という結果と変わらないということです。
次の下のセルから、値の入っているセルを探しているわけです。

バグではないのですが、VBAをいつも使っている者からすると、ワークシートの仕様を映しているだけだとしか言いようがありません。VBA全体を体系的に、Office, Excel, VBAという三つに分かれます。その中で、Excel の仕様だけ、とって付けたような感じがしますし、その分、難しい問題が表れてきます。

しかし、そっくり同じだとは言えない部分も持っています。例えば、JIS関数やCODE関数が、VBAでは、そのまま使えません。

他にもいくつかありますが、今回の　#3 の
範囲.SpecialCells(xlCellTypeBlanks)

も、この範囲を、同じ列の最後の行まで指定しても、
範囲.SpecialCells(xlCellTypeBlanks).Select

Select される範囲は、認識できる最後の行までなので、数行しかないことが分かるはずです。

お礼

解説ありがとうございます。

「Ctrl + ↓　という結果と変わらない」と言うことで、いろいろやってみました。

「値のあるセルを下に向かって探せ」というより、「値のないセルから値のあるセルに変わるセルを下に向かって探せ」という感じですね。電気回路で言うところの立ち上がりエッジを探すようなイメージですね。

解説を読んで意味がわかってきました。

私がバグではないかと思ったのは、いろいろ方法を探す中で、
「指定行から空白セルまでの行数を数えるには？」という質問に対する回答が「Range("A7", Range("A7").End(xlDown)).Rows.Count」だったり、
「xldownで探すと空白行で止まってしまうから、一番下からxlupで上に向かって探せ」
などの説明を読んでそれなりに納得していたからでした。

おかげさまですっきりしました。
また、「=MATCH(TRUE,INDEX(ISBLANK(A7:A100),,),0)-1」もすばらしいです。似たような所まで行ったのですができませんでした。

回答No.2

hige_082

Sub test()
Dim i, ii As Integer
i = 0
Do While Cells(i + 7, "a").Value <> ""
i = i + 1
Loop
Cells(1, "a").Value = i
End Sub

do　～　loop　を使用しました

お礼

マクロまで書いて頂いてありがとうございます。
でも、下の方でも書きましたが、一番知りたいのは原因なんです。
マクロを書けば簡単なんですが、できれば関数だけで何とかなりませんでしょうか。

回答No.1

zap35

とりあえずベタに

Sub Macro2()
Dim idx As Long
　For idx = 7 To 65536
　　If Cells(idx, "A").Value = "" Then
　　　Cells(1, 1).Value = idx - 7
　　　Exit For
　　End If
　Next idx
End Sub

Range("A7").End(xlDown)では
　７行目　ああああ
　８行目　（空白）
　９行目　いいいい
のときは９行目を示しますから使いにくいのではないでしょうか

お礼

回答ありがとうございます。

＞のときは９行目を示しますから使いにくいのではないでしょうか
これですが、

　７行目　ああああ
　８行目　（空白）
　９行目　いいいい

のときは「３」になります。この場合はご指摘の通り「Range("A7").End(xlDown)」が９行目を示しているのかもしれません。しかし、

　７行目　ああああ
　８行目　（空白）
　９行目　いいいい
　10行目　うううう

の時は10行目を示し、値は４になるような気がしますが、実際には値は３です。
さらに不思議なのは、

　７行目　ああああ
　８行目　いいいい
　９行目　（空白）
　10行目　うううう

とすると、値は２になります。

エクセルのバグのような気がしますがどうでしょう。
もしかするとエクセル２０００だけの現象でしょうか。