Excel オートフィルタの使用で、256列ないための、対処法は！

質問

FUJITSU ノートPCを使用しております。
FMV-BIBLO NB18D/L
Windows XP　Home Edition
Office XP Personal 2002
Excel 2002

事情（購入資金等。2007は重く、遅い。etc）があって、Office 2007は使用しないことを前提として。
そして、オートフィルタ機能を重点的に使用することを前提としてでございます。
Office 2003までは、列のデータが多くなってしまうと、256列以上のデータにオートフィルタをかけたいと思っているのにかけられない。

今、私に考えられる単純な方法は１つだけありますが、他に便利な方法がございましたらご教示下さい。

１、マクロにて、未使用の最後列の１列だけを使用しての手動的な単純なマクロしか思いつきません。
左図は、全256列を使用済です。注意：必ず、３列以上（～２０列）にオートフィルタがかかっております。
右図は、できれば、左図の最後列の後ろにつなげたいわけですが、それはできません。
各左右のシートのB列だけは、同データが入力してあります。

---【私の方法】------
●画像のように左右にきっちりと平行に表示させます。
　次に、
　マクロにて、各左右のシートの未使用の最後のあたりにある空白列の１列だけを使用しての手動的な単純なマクロを使用する。

次に、また、左シートに３列以上にオートフィルタをかけて、●のマクロを実行する。の繰り返しとなります。
---------------------

オートフィルタの操作後の同じ行番号だけを、２つのブックの各シートに即同時表示させるマクロなんてできますでしょうか？

皆様はどのような工夫をされてますか？
何か、よい方法はございますか？
もちろん、Excel自体にそのような機能はないですよね。
何卒よろしくお願い致します。

回答No.2

end-u · ベストアンサー

Sub test()
　　Dim r　 As Range
　　Dim ri　As Range
　　Dim n　 As Long
　　Dim x　 As Long
　　Dim v() As Long

　　On Error GoTo ErrHndlr
　　With Workbooks("テスト用1.xls").Sheets("sheet1")
　　　　If Not .AutoFilterMode Then Exit Sub
　　　　With .AutoFilter.Range.Columns(1)
　　　　　　n = .Cells(1).Row
　　　　　　x = .Cells.Count
　　　　　　Set r = .SpecialCells(xlCellTypeVisible)
　　　　End With
　　End With

　　ReDim v(n To n + x, 1 To 1)
　　For Each ri In r
　　　　v(ri.Row, 1) = 1
　　Next

　　With Workbooks("テスト用2.xls").Sheets("sheet1")
　　　　.AutoFilterMode = False
　　　　With .Cells(n, "IV").Resize(x)
　　　　　　.EntireColumn.ClearContents
　　　　　　.Value = v
　　　　　　.AutoFilter Field:=1, Criteria1:=1
　　　　End With
　　End With

　　Erase v
　　Set ri = Nothing
　　Set r = Nothing
　　Exit Sub

ErrHndlr:
　　MsgBox Err.Number & vbLf & Err.Description
End Sub

こんな感じになります。
シート名は仮に"Sheet1"としています。
Workbooks("テスト用2.xls").Sheets("sheet1")の IV列を作業用として使います。
ただ、.SpecialCellsにはエリア数に制限がありますので
フィルタ抽出後の不連続範囲が8192個を超えるとエラーになります。

別案として、Workbooks("テスト用2.xls").Sheets("sheet1")の作業列、例えばIV1に
=SUBTOTAL(3,[テスト用1.xls]Sheet1!A1)
という数式をセットして 1 でフィルタかけるという方法も考えられますが、
『行データが4・5万行』となるともたつきますね。

...というより256列×40,000行ものデータだと、
1シートだけでも50～70Mバイトくらいになるのではないですか？
Excelには荷が重いデータ量ですね。快適な作業ができるとはあまり思えないです。
Accessに移行して、適当なテーブルごとに分割してデータベースを構築したほうがいいような気がしますけど。

お礼

どうも有難うございました。

補足

ご回答、誠に有難うございます。
私は、今回の質問は、
「物理的にも不可能だと思っておりました」
　以前から、いろいろと悩んでもおりました。

1つだけ（前者と後者の違い）ご確認させて下さい。
＞フィルタ抽出後の不連続範囲が8192個を超えるとエラーになります。
8192行であると解釈すればよろしいですか。
●ならば、後者の=SUBTOTAL(3,[テスト用1.xls]Sheet1!A1)
を予め、記載した上でボタンで使用しようと思っておりますが、
後者では、何か不都合、エラー等が起こりますか？

自身で、いろいろ試しましたが（別段、問題はなさそうですが）、
後者なら、●印にて行えば、4・5万行でもOKだと思いますが、
＞『行データが4・5万行』となるともたつきますね。
もたつきます→前者より重くなる　ということでしょうか？

簡単で結構ですので、お時間のおありの時で結構でございます。
度々と、すみません。
-----------
p.s.前回の質問で、
各投稿欄の記載が長文かつ画面が長くなってたこともありまして、
見落としてしまいました。
私が、あわてていて、せっかくご回答して頂いた他の方（Wendy02様）に、「お礼をつける」をうっかり忘れてしまいましたことを、
この場をお借りしてお詫びと改めてお礼を申し上げます。
質問を締め切った直後に、その方のご回答の存在を発見した次第でございます。

回答No.3

end-u

＞フィルタ抽出後の不連続範囲が8192個を超えるとエラーになります。
例えば Range(1:10,12:20)、
1から10行が抽出、11行が非表示、12から20行目が抽出されていれば
エリア数は２です。行数ではありません。

効率悪いですが、エラー時に分岐処理すれば対応可です。

Sub test()
　　Dim r　 As Range
　　Dim ri　As Range
　　Dim n　 As Long
　　Dim x　 As Long
　　Dim v() As String

　　With Workbooks("テスト用1.xls").Sheets("sheet1")
　　　　If Not .AutoFilterMode Then Exit Sub
　　　　With .AutoFilter.Range.Columns(1)
　　　　　　n = .Cells(1).Row
　　　　　　x = .Cells.Count
　　　　　　ReDim v(n To n + x, 1 To 1)
　　　　　　On Error Resume Next
　　　　　　Set r = .SpecialCells(xlCellTypeVisible)
　　　　　　On Error GoTo 0
　　　　　　If r Is Nothing Then
　　　　　　　　For Each ri In .Rows
　　　　　　　　　　If Not ri.Hidden Then
　　　　　　　　　　　　v(ri.Row, 1) = "1"
　　　　　　　　　　End If
　　　　　　　　Next
　　　　　　Else
　　　　　　　　For Each ri In r
　　　　　　　　　　v(ri.Row, 1) = "1"
　　　　　　　　Next
　　　　　　End If
　　　　End With
　　End With

　　With Workbooks("テスト用2.xls").Sheets("sheet1")
　　　　.AutoFilterMode = False
　　　　With .Cells(n, "IV").Resize(x)
　　　　　　.EntireColumn.ClearContents
　　　　　　.Value = v
　　　　　　.AutoFilter Field:=1, Criteria1:="1"
　　　　End With
　　End With

　　Erase v
　　Set r = Nothing
End Sub

SUBTOTAL関数の方法は、使ってみて重くなければ、
簡単ですからいいんじゃないでしょうか。
不都合やエラーは特にないと思います。

お礼

今回もホントに以外でした。
私自身、諦めかけてた質問でしたので。
投稿してみるものなのですね！
どうも有難うございました。

回答No.1

ASIMOV

色々書かれていますが、何をやりたいのかが解りません

１．元のデーターがどうなっているのか？
２．そのデーターをつかって、どんな結果が欲しいのか？
３．マクロとは、どんな（何をする）マクロなのか？

お礼

どうも有難うございました。
不足な箇所をご指摘いただきまして助かりました。

補足

お答え有難うございます。
説明不足で、大変申し訳ございません。
少し表現（説明）がしにくい質問内容だと思います。

見にくい行列番号等だけ記載します。
テスト用1.xls　　　　テスト用2.xls
　A　B　IU　IV　　　　A　B　C　D
1　　　　　　　　　　1
2　　　　　　　　　　2
8　　　　　　　　　　8
9　　　　　　　　　　9
10　　　　　　　　　10
11　　　　　　　　　11
12　　　　　　　　　12
13　　　　　　　　　13
14　　　　　　　　　14

2007なら、256列以上ありますので、単に右を左に追記すればいいのですが、2003まではそれができません。
　2007を使用すれば、今回の質問は全く不要です。
＞１．元のデーターがどうなっているのか？
左はご覧のように、全256列をデータで使用済ですが、IT列だけ（どこの列でもいいのですが）はデータが空白です。
数万行に、ごく一般的なデータが入力されてるだけです（どんなデータでもよいです）。
　左のB列とIU列の間は、非表示にしてあるだけです。

【★1】左で、約10列以上にオートフィルタをかける単純なマクロ（私がいつも使用してます）を実行します。
　　■この実行後と全く同じ結果（行番号）を→右に即同時表示させるマクロでございます。
　　　　物理的にも不可能だと思いますが、■に近いことができるマクロがあればと思っております。
　　右で同じデータのB列に同じようにオートフィルタをかければよいと思われますが、不可能です。
　　　質問内容と画像をよくみれば分かりますが、
　　　右では項目名もデータが全く違うため【★1】は使用できません。

【私の方法】でもよいのですが、【★1】の後、オートフィルタを全て表示させて、また【★1】を実行させる必要がございます。
　これですと、【★1】の後、私が、もし【★2】手動でオートフィルタをたとえば10列に行った場合、非常に困ります、
　　【★2】を再度行わなければなりませんので、【★2】を私はもお覚えておりませんので、【私の方法】では不都合です。
　　　ただ、【★2】をしない場合は、【私の方法】でも使用できますが、行データが4・5万行ありますとすごく動作が遅くなり、
　　　かつPCに負担もかかってしまいます。　

＞２．そのデーターをつかって、どんな結果が欲しいのか？
＞３．マクロとは、どんな（何をする）マクロなのか？
■の方法でございます。
よろしくお願い致します。