オートフィルタをかけた状態で、セル内の値を参照しても、オートフィルタがかかってないセル(隠れているセル)の値を参照してしまいます。
例えば、ある条件でオートフィルタをかけて、行が1,2、6,7、8・・・となって、3,4,5行が非表示になっているのにもかかわらず、
Range("A1").Offset(0, 2).Value
を参照すると、6行目の値ではなくて、非表示の3行目の値を拾ってきてしまいます。
このように、オートフィルタをかけたシート上で値を参照する際に、非表示行を無視して行数取りし、値を参照したいのですが、何か方法はありますでしょうか?
上の例ですと、6行目のセルを3行目として扱いたいわけです。
どなたか、ご教授くださいませ。
よろしくお願い致します。
No.1ベストアンサー
- 回答日時:
例データ
コード計数
a11
b44
c56
a23
s12
a25
d72
フィルタで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で順次表示される。
ーー
十分確信は無いが、フィルタと関連付けて質問者は質問しているが
本質は、フィルター>(行の)Visibleプロパティをエクセルがいじくる、という仕組みのようなので、上記のようなコードを考え付いたわけ。
適当にCTRLを押しつつ、行を選択し、手動で書式ー行ー表示しない
にして、上記コードを実行しても似たことになる。
ーー
FilterModeなんてあるが、まず使わなくて良いと思う。
http://msdn.microsoft.com/ja-jp/library/microsof …
早速、Excel上で動作確認させていただきました。
なるほど・・・・
SpecialCells(xlCellTypeVisible).Select
で、可視セルを見てくれるということですね。
勉強になりました。
ソースも短くて、応用し易そうで、ありがとうございます。
ただ、こちらで質問したものの、結局、今回は急いでおり、
ご回答いただく前に、自力で捻り出した結果、
SendKeys "{DOWN}", True
を用いて、必要ぶんのキーボード操作させて、
セル移動で値を拾い出すことで実現してしまいました。
次回には、ぜひ、ご教授いただいたソースを活用させていただきたく思います。
どうもありがとうございました。
No.4
- 回答日時:
あとは、フィルタが掛かった状態で別シートにコピーするなんてのもあるね
非表示がコピーされないから
当初、別シートコピーのロジックも想像したのですが、
フィルタをかけては値を参照し、またフィルタをかけ直し・・・
と、何度も繰り返すため、スマートな処理で行きたいと思いました。
しかし、ご意見いただき、ありがとうございました。
No.3
- 回答日時:
こんにちは。
ちょっと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
- 回答日時:
こんなのは?
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
を用いて、必要ぶんのキーボード操作させて、
セル移動で値を拾い出すことで実現してしまいました。
次回には、ぜひ、ご教授いただいたソースを活用させていただきたく思います。
どうもありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでセルの日付を和暦表示設定にしたらおかしなことに? 3 2022/05/25 11:47
- Excel(エクセル) ピボットテーブルの参照式はオートフィル出来ない? 2 2023/03/03 15:57
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) エクセル VBAでセル内容を別の列の最下行に転記したい 2 2022/11/29 08:47
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
- その他(Microsoft Office) エクセル関数 4 2022/07/05 14:11
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Excel(エクセル) エクセルでフィルタ後、考えている場所に値コピーができない。 1 2022/05/02 21:01
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
Offsetで可視セルだけを移動したい。
Excel(エクセル)
-
マクロ オートフィルタの検索値を所定のセルから参照
Excel(エクセル)
-
VBA オートフィルター状態から見えるセルの選択について
Excel(エクセル)
-
-
4
エクセルVBAでオートフィルター最上行を取得するには
Excel(エクセル)
-
5
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
6
エラーになってないのにVBAが中断される
Excel(エクセル)
-
7
【Excel VBA】指定行以降をクリアするには?
Visual Basic(VBA)
-
8
複数指定セルの可視セルのみを別シートに転記するVBAについて
Visual Basic(VBA)
-
9
ExcelのVBAでGoToの代わりに…
Excel(エクセル)
-
10
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
11
ExcelVBAを使って、値がある場合は作業を繰り返し実行するプログラムを作成したい。
Visual Basic(VBA)
-
12
エクセルVBAでフォームのListboxをスクロールするには?
その他(Microsoft Office)
-
13
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
14
エクセルVBAでセルに入力したパスでブックを開く
Excel(エクセル)
-
15
Excelオートフィルタで複数のセルの値を参照して抽出したい
Excel(エクセル)
-
16
VBA 複数の行を高速で削除する方法
その他(プログラミング・Web制作)
-
17
任意で選択したセルをテキストボックスに表示
Excel(エクセル)
-
18
Excel vbaで別ブックのコマンドボタンをクリック
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
特定のセルが空白だったら、そ...
-
ExcelVBAを使って、値...
-
i=cells(Rows.Count, 1)とi=cel...
-
VBAでセルをクリックする回...
-
Excelのハイパーリンクにマクロ...
-
【Excel VBA】指定行以降をクリ...
-
【Excel】指定したセルの名前で...
-
Excelで指定した日付から過去の...
-
EXCELで変数をペーストしたい
-
Excel vbaで特定の文字以外が入...
-
Sub 要具ライフ() ActiveSheet....
-
TODAY()で設定したセルの日付...
-
【VBA】指定したセルと同じ値で...
-
エクセル マクロで セルの範...
-
VBAの計算で@が出てしまう件
-
DataGridViewのセル編集完了後...
-
任意フォルダから画像をすべて...
-
セルに抜けた番号の代わりに空...
-
DataGridViewで右寄せ左寄せが...
-
”戻り値”が変化したときに、マ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ExcelVBAを使って、値...
-
特定のセルが空白だったら、そ...
-
【Excel VBA】指定行以降をクリ...
-
i=cells(Rows.Count, 1)とi=cel...
-
【Excel】指定したセルの名前で...
-
Excelで指定した日付から過去の...
-
特定の文字を条件に行挿入とそ...
-
Excel VBA、 別ブックの最終行...
-
EXCELで変数をペーストしたい
-
Excelのプルダウンで2列分の情...
-
Excel vbaで特定の文字以外が入...
-
TODAY()で設定したセルの日付...
-
screenupdatingが機能しなくて...
-
DataGridViewの各セル幅を自由...
-
Sub 要具ライフ() ActiveSheet....
-
【EXCEL VBA】Range("A:A").Fi...
-
VBAを使用した時間管理
-
VBAでセルをクリックする回...
-
セル色なしの行一括削除
-
エクセルVBAでコピーして順...
おすすめ情報