Excel: Paste to visible cells

Many times you need to paste some cells to a range that includes some hidden rows. If you try this, you'll find that that it will paste to all cells, whether they are hidden or not.

Sub Copy_Paste_Visible_Cells()

    'This subroutine only handles copying visible cells in a SINGLE COLUMN

    Dim RangeCopy As Range

    Dim RangeDest As Range

    Dim rng1 As Range

    Dim dstRow As Long

    calcState = Application.Calculation

    Application.Calculation = xlManual

    Set RangeCopy = Application.InputBox("Select a range to copy ", "Obtain     Range Object", Application.Selection.Address, Type:=8)

    Set RangeDest = Application.InputBox("Select range to paste onto ", "Obtain Range Object", Type:=8)

    If RangeCopy.Cells.Count > 1 Then

        If RangeDest.Cells.Count > 1 Then

            If RangeCopy.SpecialCells(xlCellTypeVisible).Count <> RangeDest.SpecialCells(xlCellTypeVisible).Count Then

                MsgBox "Data could not be copied"

                Exit Sub

            End If

        End If

    End If

    If RangeCopy.Cells.Count = 1 Then

        'Copying a single cell to one or more destination cells

        For Each rng1 In RangeDest

            If rng1.EntireRow.RowHeight > 0 Then

                RangeCopy.Copy rng1

            End If

        Next

    Else

        'Copying a range of cells to a destination range

        dstRow = 1

        For Each rng1 In RangeCopy.SpecialCells(xlCellTypeVisible)

            Do While RangeDest(dstRow).EntireRow.RowHeight = 0

                dstRow = dstRow + 1

            Loop

            rng1.Copy RangeDest(dstRow)

            dstRow = dstRow + 1

        Next

    End If

    Application.CutCopyMode = False

    Application.Calculation = calcState

End Sub

If you want to do this quickly just using the keyboard, there's a few steps you can follow (from https://spreadsheetplanet.com/paste-filtered-column-skipping-hidden-cells/):

1. Select the Range G3:G24.

2. Press Alt + ; at the same time to select only visible cells.

3. In the formula bar type =C3 (C3 is the first cell with filtered price), and press the Ctrl + Enter key simultaneously to fill all selected cells. Then you will see the filtered prices are pasted to the specified range with ignoring the hidden cells and rows.

3. Unhide all the cells and select them.

4. Copy all the cells.

5. Paste as values to convert the formulas into text.