《エクセル2000》オートフィルタで寄せたものに連番を振りたいのですが

質問

オートフィルタの条件で抽出したデータのみ連番を振りたいのですが、上から1、2、3と入力し後はオートフィルで入力しようとしても全部1になってしまい、うまくいきません。

どうすればいいのでしょう？

回答No.5

tarodaro · ベストアンサー

>これがうまくいきません
確かに... orz

申し訳ないですが、問題が解決しない場合、下記コードを
標準モジュールに貼り付けて実行してください
(事前に対象範囲を選択しておきます)

Sub Macro1()
Dim r As Range
Dim t As Range

Application.ScreenUpdating = False
Set r = Selection.SpecialCells(xlCellTypeVisible)
For Each t In r
t = t.Value
Next
Application.ScreenUpdating = True
End Sub

番号を振るマクロは、いまいち動作に確信が持てなかったので保留です
ごめんなさい

お礼

>確かに... orz
ああっ、そんなに落ち込まないで下さい（笑）
お陰様で解決しました。こちらのマクロも永久保存です。
もう少し締め切らずにおきますが、本当にありがとうございました。

回答No.13

haccyan

こんばんは。

たいへんそうですね。
仮にE列を作業列として、
> 全部1になってしまい、
をまずやってください。
オートフィルタで寄せた状態の可視セルのE列に”１”が入ります。
次にオートフィルタを解除してすべて表示に戻してください。
次にA2以下に連番を振るとして、
A2=IF(E2="","",COUNT(E$2:E2))をデータのある最下行までフィルコピー。
以上で、オートフィルタで寄せた状態の可視データのみに連番をふることができます。
後は、A列を値のみ上書きコピーすればOKです。

もし勘違いしていたとしたらすいません。

お礼

可視の行のみ数値が入っている列がありますので、こちらの関数も使えそうです。
皆様のおかげで、解決にこぎつけることができました。
ありがとうございました。

回答No.12

noname#204879

[No.11回答に対するお礼/補足]に対するコメント、

コピーしたいのは特定の列の可視セルのみであること、了解。話を簡単にするために、連番の列とその隣の列の２列だけにしておきましょ。

》 フィルターを解除した時もその連番を保持しておき
》 たいため、コピー→値でペーストしたいのです。

分かってますよ。

》 単一セル指定とは、コピーしたい行の一番上のセル
》 のみを選択するということでよろしいでしょうか？

[貼り付け]先の範囲の左上隅のセルのみを選択すること。

》 「一番上のセルだけを選ぶと、可視セルではないセ
》 ルにも貼り付けられてしまいます。」

どうも言い回しが変だ（話が噛み合わない）と思ったら今気が付きました。
貴方の[貼り付け]先は、[オートフィルタ]が掛けられた行範囲（例えば右側上方）にしていますね。そして、[貼り付け]操作をした後も、[オートフィルタ]を掛けたまんまにしているのですね。[オートフィルタ]を解除しないと駄目ですよ。解除したくないなら、[オートフィルタ]の影響を受けていないズーッと下の範囲（可能なら、別シートの上方にでも）に[貼り付け]なきゃ。

お礼

どうやら、希望している操作は難しいようですね。
関数やVBAに頼らない、一発技があるとベストだったのですが。
でも、お陰様で無事解決しました。ありがとうございました。

回答No.11

noname#204879

[No.10回答に対するお礼]に対するコメント、

いささか疲れてきましたが…（ギブアップしそう）

》 これを行うと可視セル全てが選択されてしまう…

それでなぜ好けないのですか？
可視セルのみをコピーしたいのでしょ？
「可視セルのみ」＝「可視セル全て」でしょ？

》 ２）コピー先を同じ行数分範囲指定→「可視セルを選択」

「同じ行数分範囲指定」を「単一セル指定」にしたら？
なぜ、また→「可視セルを選択」したの？何のために？

お礼

本当に何度もすみません…申し訳ないので締め切ろうかとも考えましたが、うやむやのまま切るのはすっきりしないかもと思い、補足します。
このまま2～3日締め切らずにおきますので、もしご納得されました場合は放置していただけますか。

>可視セルのみをコピーしたいのでしょ？
いえ……ごめんなさい、そうではなく、特定の行だけです。
質問文とNo.2のお礼欄をお読みいただければいいかと思いますが、最終目的はフィルターで寄せた可視の行のみ、連番で番号を振ることです。
SUBTOTAL関数で連番を振った、ある特定の行のみを、フィルターを解除した時もその連番を保持しておきたいため、コピー→値でペーストしたいのです。
他の行は処理に必要な関数が入っていますので、そちらはそちらで値になってしまうと、困ったことになってしまいます。

>「同じ行数分範囲指定」を「単一セル指定」にしたら？
単一セル指定とは、コピーしたい行の一番上のセルのみを選択するということでよろしいでしょうか？
その場合はNo.9のお礼欄に書きましたとおり「一番上のセルだけを選ぶと、可視セルではないセルにも貼り付けられてしまいます。」という結果になります。

多大なるお時間を割いていただき、感謝しております。

補足

すみません、表現間違いました。

特定の行だけです。
↓
可視セルの特定の“列”だけです。

回答No.10

noname#204879

[No.9回答に対するお礼]に対するコメント、

》 １：フィルターで寄せた可視セルをコピー

[コピー]する直前に、
[編集]→[ジャンプ]→[セル選択]で“可視セル”に目玉入れて[OK]をクリック
を実行してみてください。その後[コピー]するのです。
そうすれば「可視でないセルに」でなく「可視でないセルが」「貼り付けられてしまい」にはならないと思います。（テニヲハを間違えないように）

お礼

＞[編集]→[ジャンプ]→[セル選択]で“可視セル”に目玉入れて[OK]をクリック
を実行してみてください。その後[コピー]するのです。

すみません、これを行うと可視セル全てが選択されてしまうのですが…。

ちなみに
１）範囲指定をした後、ツールバー内の「可視セルを選択」してコピー
２）コピー先を同じ行数分範囲指定→「可視セルを選択」
して普通に貼り付けようとすると、
「そのコマンドは複数の選択範囲に対して実行できません」とエラーが出ます。

回答No.9

noname#204879

[No.6回答に対するお礼/補足]に対するコメント、

》 メッセージは「領域が違う」とは違うかもしれません

正しいメッセージをお知らせください。誤った情報は第三者を混乱させます。

》 ちなみに当方エクセル2000です。

私が試していたのは Excel 2002 でしたが、同じことを Excel 2000 で試しました。結果は後述。

[No.4回答に対するお礼]の中で、「値として貼り付けのコピーが前提となる」と仰っていましたが、その理由もお知らせください。

》 フィルターで寄せたまま、可視セルだけを値でコピ
》 ーできますか？

ていうか、可視セルだけを（通常の）[貼り付け]すると、連番の =SUBTOTAL()部分は結果的に[値の貼り付け]になります。

さて、Excel 2000 の場合、[オートフィルタ]で抽出された（結合セルを含む）レコードを別の場所に[値の貼り付け]を実行すると、「この操作には、同じサイズの結合セルが必要です」と叱られて、コピーに失敗します。通常の[貼り付け]は問題なく可能です。
Excel 2002 の場合、同様のコピー失敗は発生しません。

》 セルの結合はありません

そうですか。何れにしても、コポー失敗時に何というメッセージが出るのかを知りたいものです。

お礼

何度もありがとうございます。

まずは、エラーメッセージの件につきまして。
再度試してみたところ、
１：フィルターで寄せた可視セルをコピー（仮に10行×1列とします）
２：その隣の行、可視セルを10行×1列を選択

以上の前作業を行ったのち「値をペースト」しようとすると「コピー領域と貼り付け領域の形が違うため、情報を貼り付けることができません」と出ます。

かといって、一番上のセルだけを選ぶと、可視セルではないセルにも貼り付けられてしまいます。

＞ていうか、可視セルだけを（通常の）[貼り付け]すると、連番の =SUBTOTAL()部分は結果的に[値の貼り付け]になります。

こちらも試してみました。確かにそのまま「貼り付け」で、値として貼り付けられましたが、可視でないセルにも貼り付けられてしまい、貼り付けたい行に張り付きません。惜しいです。

値として貼り付けたいのは、フィルターを解除した時も寄せた時と同じ番号がそのまま残っていて欲しいためです。

以上補足致します。

回答No.8

imogasi

オートフィルタという操作に拘らないなら
全て表示の状態で
=IF(A2="a",MAX($B$1:B1)+1,"")
と入れて、最終行まで複写でよいと思う。
aは条件の文字列で大小、含む、でないなどが＝の代わりに使えるし
A列はa,かつB列はbなどの条件も使えます。
これを式の値＝C1とでもすれば、C1の文字列数字を変えると、条件が変えられます。
ニーズに合っているかどうか判らないが。

お礼

imogasiさん、お久しぶりです。
なるほど、フィルターにかけている条件をそのまま関数にということですね。
今回条件がかなり複雑ですので難しそうですが、フィルターにかける条件が単純な時に是非こちらを使わせていただきます。
どうもありがとうございました。

回答No.7

tarodaro

>解決しました。
良かった！！ （涙）

>もう少し締め切らずにおきますが
妙手が見つかるといいですね

下記マクロ「連番君」、保証はしかねるブツですが
もしよろしければご笑納ください
(テストした限りでは大丈夫かな？とは
思うのですが)

'選択範囲の可視セルに連番を振ります
Sub Macro2()
Dim areaR As Range
Dim dstR As Range

Application.ScreenUpdating = False
Set areaR = Selection.SpecialCells(xlCellTypeVisible)
Dim i As Long, cnt As Long
cnt = 1
Dim s As Range
For Each dstR In areaR.Areas
For i = 1 To dstR.Count
dstR.Cells(i, 1) = cnt
cnt = cnt + 1
Next
Next
Application.ScreenUpdating = True
End Sub

お礼

こちらも早速保存しました。汎用性があるので、きっと今後出番はあると思います。

VBA初心者のくせに、このサイトのおかげで職場ではいっぱしのマクロ使いの顔をしております(^^;)

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

回答No.6

noname#204879

[No.4回答に対するお礼]に対するコメント、

》 その場合は「領域が違う」と言われてコピーできま
》 せん。

「その場合」と「領域が違う」は関係ないのでは？
私がテストした限りでは、仰るような現象は出ません。
セルの結合を多用していませんか？

お礼

色々試していたので、メッセージは「領域が違う」とは違うかもしれません、ごめんなさい。
ですが、コピーができないのは本当です。
フィルターで寄せたまま、可視セルだけを値でコピーできますか？
ちなみに当方エクセル2000です。

補足

あ、セルの結合はありません。
以上補足致します。

回答No.4

noname#204879

[No.2回答に対するお礼]に対するコメント、

No.3では、No.2と全く同じ回答になってしまって申し訳ありませんでした。

さて、[オートフィルタ]を実行して抽出されたデータを[コピー]して、他の空いた場所に[貼り付け]たらどうなりますか？

お礼

ありがとうございます。
１：フィルタを寄せたまま
２：値として貼り付け
のコピーが前提となるのですが、その場合は「領域が違う」と言われてコピーできません。
かと言って、セル一つだけを選んでペーストすると、フィルタで寄せていないセルに番号が入ってしまいます。

補足

わかりにくそうなので補足します。

「フィルタで寄せていないセル」＝「フィルタで寄せた時に隠されている、不可視のセル」です。

回答No.3

noname#204879

１行目は項目(名が入力された)行で、データは２行目以降にあるものとします。空白行が全くない列に着目して、それがＣ列と仮定します。そして、「連番を振りたい」列がＡ列であると仮定します。
[オートフィルタ]を実行していない（すべてのデータが表示されている）状態で、セル A2 に式 =SUBTOTAL(3,C$2:C2) を入力して、此れを下方にズズーッと複写してください。

お礼

ご回答ありがとうございます。
目下の問題をNo.2さんのお礼欄に書いたのですが、もしよろしかったらお智恵を拝借できますでしょうか。