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

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

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

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

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

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

これがしたい！

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

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

流れとしては、

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

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

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

ちなみに、

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

このパターン

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

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

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

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

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」ボタンを押すか、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ブックを保存する場合

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

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

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

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

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

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

おわりに

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

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