エクセルで可視セルにのみ値貼り付けをする方法があったら教えて下さい やりたいこととしては フィルター

エクセルで可視セルにのみ値貼り付けをする方法があったら教えて下さい

やりたいこととしては
フィルターで条件を絞った表に関数を入れ値を出す→(関数無しの)数値のみにする
なのですが、関数で出した値(数値)をコピーして同じ範囲に値貼り付けしようとすると関係の無いセルに値が入ってしまって困っています。

行数が多いので、フィルターを解除しての値貼り付けは厳しいです。

No.5 回答者： fujillin 回答日時： 2023/10/01 17:48

No4です。

＞Set r = Intersect(Selection, ActiveSheet.AutoFilter.Range)
＞の行です
多分、Selection か AutoFilter.Range が取れてないってことだと思いますけれど・・

Office2019 の場合は AutoFilter をかけていないと、ActiveSheet.AutoFilterがNothingになるので、前の行でそれをチェックしています。
（365では仕様が変わっているのかも知れませんね）

Selection はユーザの選択範囲ですが、セル範囲を選択していればエラーにはならないはずと思いますが、何か別の図形などを一緒に含んでしまっていることはありませんか？


多少チェックポイントを増やしてみた例を以下に示しておきます。

もしも、以下で『オートフィルタ範囲を取得できません』の表示が出るなら、365と2019の仕様の違いだと思われますので、当方の環境では確認できません。
その場合には、オートフィルタの範囲を取得できさえすれば良いので、直接Rangeで指定するようにしてしまえば、とりあえずは動作すると思います。

Sub sample()
Dim r As Range, c As Range

If ActiveSheet.AutoFilter Is Nothing Then MsgBox "オートフィルタが設定されていません": Exit Sub

On Error Resume Next
Set r = ActiveSheet.AutoFilter.Range
If Err.Number <> 0 Or r Is Nothing Or TypeName(r) <> "Range" Then _
MsgBox "オートフィルタ範囲を取得できません": Exit Sub
If TypeName(Selection) <> "Range" Then MsgBox "選択範囲が不正です": Exit Sub
On Error GoTo 0

Set r = Intersect(Selection, r)
If r Is Nothing Then MsgBox "有効な範囲が指定されていません": Exit Sub

For Each c In r.Cells
If Not c.EntireRow.Hidden Then c.Value = c.Value
Next c

End Sub

No.4 回答者： fujillin 回答日時： 2023/09/29 18:04

No3です。

365環境はわかりませんけれど、デバッグモードがないってことでしょうか？

短いコードなので、ステップインなどで1行ずつ実行しても問題の行はわかると思いますけれど・・

この回答へのお礼

失礼しました。デバックモードありました。
Set r = Intersect(Selection, ActiveSheet.AutoFilter.Range)
の行です

お礼日時：2023/10/01 17:03

No.3 回答者： fujillin 回答日時： 2023/09/28 21:08

No2です。

＞どこかの行が引っかかってるというわけは無さそうです
状況がわかりかねます。

実行できずに、エディターに入力した時点でエラー表示になるってことでしょうか？
それとも、実行はできるけれどエラーが表示されるってことでしょうか？
後者なら、「どこの行か不明」ってことはないと思いますけれど・・

前者の場合は、バージョン等環境の違いかもしれません。
ちなみに、当方は Office2019 ですが、問題なく実行できます。

この回答へのお礼

入力した時点では問題なく実行も出来ます。
エラーが表示された際、特定の行に色が付いていないのでどこの行か不明なのですが
エラーになった行を特定できる機能があるのならお手数ですが教えて頂きたいです
Office365です

お礼日時：2023/09/29 17:02

No.2 回答者： fujillin 回答日時： 2023/09/26 18:10

No1です。

＞フィルターをかけVBAで列を選択し、やってみたのですが
＞エラー　424
＞オブジェクトが必要です
＞が出てしまいました
あら・・・
出そうなものはチェックしたつもりだったのですが、
どの行でエラーになっているのでしょうか？


すみません。No1のコードにタイポがありました。
これが原因とは限らないのですが、訂正しておきます。

＞Dim f As Range, c As Range
　　　↓　　↓　　↓
　Dim r As Range, c As Range

※ ちなみに当方の環境では、No1のままのコードでも、
　処理はできエラーはでませんね。
　（とは言え、訂正はしておいてください）

この回答へのお礼

ありがとうございます。
直しましたがやはり同じエラーです
どこかの行が引っかかってるというわけは無さそうです
VBAは大分初心者なので見落としてるところがあるのかもしれません

お礼日時：2023/09/28 17:36

No.1 回答者： fujillin 回答日時： 2023/09/26 10:50

こんにちは

手作業でやるには、一発では無理そうですね。
若干手間が掛かりますが、ソート機能を利用すればひとつずつ処理するよりは速いでしょう。

1）フィルターをかける
2）空き列に何らかのマークを入力し、最終行までフィルコピー
3）フィルターを外して、マークでソート
以上で、対象の行だけ固まりますので、その範囲をまとめてコピー、値をペースト

4）再度ソートで元の順序に戻す
（元の順を記録しておくために、ナンバリングの列が必要になります）



「そんなかったるいことやってらんねぇ！」という場合はVBAで。
フィルターをかけた状態で、値に変換したい列を選択して以下を実行することで、可能と思います。
（フィルターがない場合や、対象範囲が選択されていなければなにもしません）

Sub sample()
Dim f As Range, c As Range
If ActiveSheet.AutoFilter Is Nothing Then Exit Sub
Set r = Intersect(Selection, ActiveSheet.AutoFilter.Range)
If r Is Nothing Then Exit Sub

For Each c In r.Cells
If Not c.EntireRow.Hidden Then c.Value = c.Value
Next c
End Sub

この回答へのお礼

ありがとうございます。
フィルターをかけVBAで列を選択し、やってみたのですが
エラー　424
オブジェクトが必要です
が出てしまいました

お礼日時：2023/09/26 17:44