Pasting Data on Filtered Rows

When we paste data on filtered rows 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.

So let’s see an example, below is a screenshot of a filtered data:

When you simply use Copy and paste it looks like this:

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 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

3. Click F5 key or the Run button, a dialog box will appear for you to select Range to copy

4. After selecting the range, click OK, and then another dialog box will appear for you to select a range to paste the copied data in.

5. Click OK to get your desired result.

Download the same file here.

588 total views, 3 views today