ExcelVBA:オートフィルタをかけたシート上でのセル内の値参照について

質問

オートフィルタをかけた状態で、セル内の値を参照しても、オートフィルタがかかってないセル（隠れているセル）の値を参照してしまいます。

例えば、ある条件でオートフィルタをかけて、行が１，２、６，７、８・・・となって、３，４，５行が非表示になっているのにもかかわらず、

Range("A1").Offset(0, 2).Value

を参照すると、６行目の値ではなくて、非表示の３行目の値を拾ってきてしまいます。

このように、オートフィルタをかけたシート上で値を参照する際に、非表示行を無視して行数取りし、値を参照したいのですが、何か方法はありますでしょうか？

上の例ですと、６行目のセルを３行目として扱いたいわけです。

どなたか、ご教授くださいませ。
よろしくお願い致します。

回答No.1

imogasi · ベストアンサー

例データ
コード 計数
a 11
b 44
c 56
a 23
s 12
a 25
d 72
フィルタでaを選択
そして
ーー
Sub test01()
Dim r As Range
d = Range("a65536").End(xlUp).Row
Range("A2:A" & d).SpecialCells(xlCellTypeVisible).Select
For Each cl In Selection
MsgBox cl.Offset(0, 1)
Next
End Sub
を実行すると
11
23
25
がMsgboxで順次表示される。
ーー
十分確信は無いが、フィルタと関連付けて質問者は質問しているが
本質は、フィルター＞（行の）Ｖｉｓｉｂｌｅプロパティをエクセルがいじくる、という仕組みのようなので、上記のようなコードを考え付いたわけ。
適当にＣＴＲＬを押しつつ、行を選択し、手動で書式ー行ー表示しない
にして、上記コードを実行しても似たことになる。
ーー
ＦｉｌｔｅｒＭｏｄｅなんてあるが、まず使わなくて良いと思う。
http://msdn.microsoft.com/ja-jp/library/microsoft.office.tools.excel.worksheet.filtermode(VS.80).aspx

お礼

早速、Excel上で動作確認させていただきました。

なるほど・・・・
SpecialCells(xlCellTypeVisible).Select
で、可視セルを見てくれるということですね。
勉強になりました。
ソースも短くて、応用し易そうで、ありがとうございます。

ただ、こちらで質問したものの、結局、今回は急いでおり、
ご回答いただく前に、自力で捻り出した結果、
SendKeys "{DOWN}", True
を用いて、必要ぶんのキーボード操作させて、
セル移動で値を拾い出すことで実現してしまいました。
次回には、ぜひ、ご教授いただいたソースを活用させていただきたく思います。

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

回答No.4

pulsa

あとは、フィルタが掛かった状態で別シートにコピーするなんてのもあるね
非表示がコピーされないから

お礼

当初、別シートコピーのロジックも想像したのですが、
フィルタをかけては値を参照し、またフィルタをかけ直し・・・
と、何度も繰り返すため、スマートな処理で行きたいと思いました。
しかし、ご意見いただき、ありがとうございました。

回答No.3

end-u

こんにちは。
ちょっとFunction化してみました。

Sub try()
　　MsgBox vOffset(Range("A2"), 2, 1).Address
　　MsgBox vOffset(Range("A2"), 2).Address
　　MsgBox vOffset(Range("A2"), , 2).Address
　　MsgBox vOffset(Range("A2"), -1).Address
　　MsgBox vOffset(Range("A2"), -2).Address 'error
End Sub

Function vOffset(ByRef r As Range, _
　　　　　　　　 Optional ByVal y As Long = 0, _
　　　　　　　　 Optional ByVal x As Long = 0) As Range
　　Dim n　 As Long
　　Dim i　 As Long
　　Dim cnt As Long

　　On Error GoTo errHndlr
　　n = IIf(y > 0, 1, -1)
　　Do Until y = cnt
　　　　i = i + n
　　　　If Not r.Offset(i).EntireRow.Hidden Then cnt = cnt + n
　　Loop
　　Set r = r.Offset(i)
　　i = 0
　　cnt = 0
　　n = IIf(x > 0, 1, -1)
　　Do Until x = cnt
　　　　i = i + n
　　　　If Not r.Offset(, i).EntireColumn.Hidden Then cnt = cnt + n
　　Loop
　　Set r = r.Offset(, i)
errHndlr:
　　Set vOffset = r
　　If Err.Number <> 0 Then MsgBox Err.Number & vbLf & Err.Description
End Function

（行数多いと時間かかります。＆エラー処理はもう少し工夫したほうが。）

お礼

行だけでなく、列までも。。。ご回答ありがとうございます。

こちらも、表示・非表示をIf文判定させるわけですね。

結局のところ、ご回答いただく前に、自力で捻り出してしまい、
SendKeys "{DOWN}", True
を用いて、必要ぶんのキーボード操作させて、
セル移動で値を拾い出すことで実現してしまいました。

Function化いただき、ありがとうございます。
次回には、ぜひ活用させていただきたく思います。

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

回答No.2

merlionXX

こんなのは？

Sub test01()
Dim x As Range
Dim i As Long
Set x = Range("A1")
Do
Set x = x.Offset(1, 0)
If x.EntireRow.Hidden = False Then '非表示行なら
i = i + 1 'カウント
End If
If i = 2 Then '表示行で2番目なら
MsgBox x.Value
Exit Do 'LOOPを抜ける
End If
Loop
End Sub

お礼

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

表示行／非表示行をIf文判定させて、値を参照するわけですね。
なるほど、確かにありです。

同じくで恐縮なのですが、
結局、こちらで質問したものの、今回は急いでおり、
ご回答いただく前に、自力で捻り出した結果、
SendKeys "{DOWN}", True
を用いて、必要ぶんのキーボード操作させて、
セル移動で値を拾い出すことで実現してしまいました。
次回には、ぜひ、ご教授いただいたソースを活用させていただきたく思います。

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