Pasting Data on Filtered Rows

When we paste data on filtered rows in Excel it always past it on both visible and hidden rows. And if you are doing this for a huge set of data then I am sure you must be pasting it manually and wasting lot of time and efforts. Don’t worry guys, we have a solution for this but only through VBA.

When you simply use Copy

So it will get pasted on hidden rows also, to avoid the same, please follow the below steps :

1.  Press Alt + F11 keys simultaneously, and a Microsoft Visual Basic for Applications (VBA) window will get open.

2. Click Insert > Module, then paste below VBA code to the Module window.

Sub PasteOnVisibleCells()

    Dim rg1 As Range

    Dim rg2 As Range

    Dim CopyRg As Range

    Dim PasteRg As Range

    Set CopyRg = Application.Selection

    Set CopyRg = Application.InputBox("Copy Range :", , CopyRg.Address, Type:=8)

    Set PasteRg = Application.InputBox("Paste Range:", , Type:=8)

    For Each rg1 In CopyRg

        rg1.Copy

        For Each rg2 In PasteRg

            If rg2.EntireRow.RowHeight > 0 Then

                rg2.PasteSpecial

                Set PasteRg = rg2.Offset(1).Resize(PasteRg.Rows.Count)

                Exit For

            End If

        Next

    Next

    Application.CutCopyMode = False

End Sub