煩わしいパスワード管理から解放されよう!

【関数あり】エクセルでフィルター適用後の見えているセルのみにコピペする方法

可視セルのみに貼り付け

エクセルのフィルター機能はかなりよく使う機能だと思いますが、

フィルター機能で絞り込んだ後に、複数の範囲をコピーして貼り付けたら、非表示のセルを含んで貼り付けられてしまいます。

もともと、そのような形にならないように工夫すればいいのですが、

大きな表などでこの場面に直面してしまい、手で写していくなんてやってられませんよね。

そこで、オリジナルの関数を作成して対処しましたので、よかったら使ってください。

コピペできないなんて納得できない!このためだけの関数を作ってやったぜ。

これがしたい!

可視セルのみに範囲を貼り付け

可視セルのみに貼り付けする方法

フィルター適用後の可視セルのみに貼り付けする方法(オリジナル関数)を紹介していきます。

流れとしては、

  • VBAで可視セルのみに貼り付ける関数を作成
  • 右クリックメニューに追加
  • データをコピーして右クリックメニューから貼り付ける

というように貼り付けを行います。

内容が分かりにくかった人は、この記事で解説に使った例をこちらからダウンロードいただけます。

MEMO
本記事で紹介する内容を行う場合は、自己責任でお願いいたします。

ちなみに、

範囲ではなく、一つのセルだけをコピーして貼り付けなら、デフォルトで可視セルのみに貼り付け可能ですよ。

このパターン

一つのセルの貼り付けは可視セルのみに反映できる

可視セルのみに貼り付ける関数を作成

まずは、可視セルのみに貼り付ける関数をVBAを使用して作成します。

VBAといっても下記の手順通りにコピペしてもらえればOKです!

Alt+F11を押すか、「開発」タブの「Visual Basic」からVBEを起動します。

開発タブが表示されていない場合は「開発タブを表示する方法」の方法で追加することができます。

エクセルのVBEを起動する
VBEとは
エクセルに標準で搭載されているVBAを編集するためののエディターのことです。

VBEが起動したら、「Microsoft Excel Objects」の上で右クリックし、「挿入」項目の「標準モジュール」をクリックしましょう。

標準モジュールを挿入する

新しく「Module1」が作られ、右側がコードの入力ウィンドウとなるので、下記のコードをコピーして貼り付けてください。

可視セルのみに貼り付ける関数
'' 右クリックメニューに追加
Sub AddMenu()
    Dim Newb
    Set Newb = Application.CommandBars("Cell").Controls.Add()
    With Newb
        .Caption = "可視セルに範囲で貼り付け"
        .OnAction = "AppearRangePaste"
        .BeginGroup = True
    End With
End Sub

'' 右クリックメニューから削除
Sub DelMenu()
    Application.CommandBars("Cell").Controls("可視セルに範囲で貼り付け").Delete
End Sub

'' 可視セルのみに貼り付ける関数
Sub AppearRangePaste()
    Dim ClipBoardForm As Variant
    Dim cbData As New DataObject
    Dim vbDataSepa As Variant
    
    ClipBoardForm = Application.ClipboardFormats
    If ClipBoardForm(1) = -1 Then
        MsgBox "クリップボードは空です。", vbExclamation
        Exit Sub
    End If
    
    cbData.GetFromClipboard
       
    vbDataSepa = Split(cbData.GetText, vbCrLf)
    
    Dim R As Long, C As Long
    R = Selection.Row
    C = Selection.Column
    
    Dim i As Long
    Dim Ri As Long
    Ri = 0
    
    Do While i <= UBound(vbDataSepa)
        If Rows(R + Ri).Hidden <> True Then
            ActiveCell.Offset(Ri, 0) = vbDataSepa(i)
            i = i + 1
        End If
        Ri = Ri + 1
    Loop
    
 End Sub
コードを貼り付け後

次に、「ツール」の「参照設定」をクリックます。

ツールの参照設定をクリック

Microsoft Forms 2.0 Object Library」にチェックを入れて「OK」ボタンを押すか、

Microsoft Forms 2.0 Object Library

参照」ボタンから「FM20.DLL」を選択して「開く」を押して「OK」でも可能です。

FM20DLLを開く

右クリックメニューに追加する

右クリックメニューに、作成した関数を追加するために、「” 右クリックメニューに追加」より下のAddMenu部分をクリックします。

(右上の欄にAddMenuと表示されていればOKです。)

その状態でF5をクリックすると、右クリックメニューに追加されます。

右クリックメニューに追加する

なにも表示されないけど登録できているはずだよ。

Alt+F11を再度押すと、エクセルの通常画面が表示されるので、任意のセルで右クリックして確認してみましょう。

可視セルに範囲で貼り付け」が表示されています。(まだ押さないでね。)

右クリックメニューに追加されている

可視セルのみに貼り付ける

右クリックメニューに追加できたら、いよいよ、コピペを行います。

まずは、貼り付ける元となるデータ範囲を選択してCtrl+Cコピーしましょう。

データをコピーする

続いて、貼り付ける範囲の一番上のセルで右クリックし、追加した「可視セルに範囲で貼り付け」をクリックします。

可視セルのみに貼り付ける

これで、コピーした範囲が見えるセル部分のみに貼り付けられます。

貼り付けの結果

上手くいったかな??

右クリックメニューを削除する

最後に、右クリックメニューに追加した項目を削除しておきましょう。

” 右クリックメニューから削除」より下のDelMenu内のいずれかの場所でクリックし、(右上の欄にDelMenuと表示されていればOKです。)

その状態でF5をクリックすると、右クリックメニュから削除されます。

追加したメニューを削除する

右クリックメニューが残っていると良くないので、忘れずに削除しておいてね。

ブックを保存する場合

「.xlsx」で作成されたブックの場合、今回追加したコードがあるままでは、保存時にこのようなメッセージが表示されます。

いいえを選択してマクロありのブックとして保存する
MEMO
このエラーは、「.xlsx」というVBAが保存できないブックにVBAの情報を保存しようとするために表示されます。詳しくは「次の機能はマクロなしのブックに保存できませんが表示される原因と対処」にまとめてあります。

マクロなしのブックとして保存する

今回の関数は、その場しのぎの関数追加という感じなので、このコードを含めて保存する必要はありません。(むしろ不要ですよね。)

なので、保存時に表示される下記のメッセージは、「はい」を選択しても大丈夫です。

このページをブクマしておいて、必要になった時に貼り付けるのがおあすすめです。

マクロ有効ブックとして保存する

不要だとは思いますが、VBAも合わせて保存したいのであれば、「いいえ」を選択し、「ファイルの種類」を「.xlsm」に変更して保存しましょう。

xlsmの拡張子を選択して保存する

おわりに

エクセルのフィルター機能で絞り込んだ後に、複数の範囲をコピーして貼り付けたら、非表示のセルを含んで貼り付けられてしまう現象を解決するオリジナルの関数を紹介しました。

作り込んだ関数ではなく、このためだけにとりあえず作成した関数なので、使用する際は十分に気を付けてください。

おかしな挙動があったら修正するので教えてくださいね。

7 COMMENTS

aa

お世話になっております。貴VBAを使用しようと思いました。
しかしながら、複数列の可視化コピーを行う場合にVBAを使用すると、
1行に複数セルが入力されてしまいます。
対応方法等ご教授いただければ幸いです。

返信する
おーちゃん

コメントありがとうございます^^
こちらで紹介しているVBAは「フィルターでの表示/非表示のセル」を対象としているので、複数”行”をコピペすることを想定して作っております。
なので、複数列でのコピペではうまく機能しません。
複数列で実行する場合は、VBAの区切り文字とOffset方向を変更すれば一応は動作すると思います。
後で改めて記事に追記しておきます。

返信する
匿名

こちらと同じ悩みで使用させていただきました。
ありがとうございます。
追記を楽しみにお待ちしております。

返信する
YYN

お世話になっております。
上記の記事がとても参考になったのですが、中途半端な状態で元表を削除してしまい、右クリックメニューに使用できない「可視セルに範囲で貼り付け」が複数残ってしまっているのですが、削除方法をご教示いただくことは可能でしょうか。
お忙しいところ恐縮ですが、何卒よろしくお願いいたします。

返信する
おーちゃん

コメントありがとうございます^^

右クリックメニューから削除するには、
記事内項目の「右クリックメニューを削除する」を参照いただき実行いただければと思います。

複数個メニューが残っている場合は、複数回実行お願いします。

返信する
匿名

手順通りにやってみましたが、右クリックメニューに追加されませんでした。
オプションからマクロは有効になっていると確認したのですが、何か理由が考えられたりしますでしょうか?

返信する
おーちゃん

コメントありがとうございます。
下記の要因が考えられますが、いかがでしょうか?
・複数のエクセルを開いていて、VBAで追加したのが別のエクセルモジュールに追加している。
・F5でメニューに追加実行時、別の関数(AddMenu以外)で実行してしまっている。

返信する

コメントを残す