Pasting into visible cells only

Hi There,

I am currently heading up a project dealing with excel, wherein rows of data must be examined and a decision on whether to keep said data is made and written in the last column of each row. There are about 65000 rows of data.

There are many rows that could be cleared out preemptively, so that was done before any of the other rows were looked at, and those rows were filtered so as to not appear and clutter up space.

 

There are 2 other people on the project, so I've been simply copying the visible rows in sections and pasting them into new, smaller documents for them to work on. Once they have completed that, they email me back the document and I send them another one.

Here is where the issue occurs. When I attempt to paste the data back into the original filtered list, the rows paste over the hidden rows as well as the visible ones, messing up my data.

Is there any way for me to paste this last column of data onto the visible rows only, either through a Macro or base functionality in excel, or do I have to do this manually 65000 times?

 

Thanks much

 

 

Question Info


Last updated November 8, 2018 Views 201,108 Applies to:
Answer

Select the cells to copy, run this code:

 

Sub Copy_Filtered_Cells()
    Set from = Selection
    Set too = Application.InputBox("Select range to copy selected cells to", Type:=8)
    For Each Cell In from
        Cell.Copy
        For Each thing In too
            If thing.EntireRow.RowHeight > 0 Then
                thing.PasteSpecial
                Set too = thing.Offset(1).Resize(too.Rows.Count)
                Exit For
            End If
        Next
    Next
End Sub

189 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.