Pasting of cells to exclude hidden lines/rows

Bryce456

New Member
Joined
Jan 24, 2005
Messages
4
I am attempting to copy groups of columns and then paste their contents over columns with similar formatting while preserving formulas in hidden rows on my destination sheet. I have been successful in copying ranges of cells including only the visible cells,
(using this process: Select the whole range of cells -including the hidden ones. Go to Edit>GoTo>Special>VisibleCellsOnly.
Copy and paste.)
but now I wish to paste those same cells on only visible cells. Any help or ideas would be greatly appreciated. Thank you,

Bryce
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Bryce,

I don't believe there is a way to do this with a built-in Copy and Paste operation. However, it should be easy to do it with a custom macro. If you are interested I could provide some code. One thing is not yet clear to me, though. What do you want to happen to the data that would have been pasted into the hidden rows? Should it be skipped? Or was your intention to have it go to the next visible row?

Damon
 

Bryce456

New Member
Joined
Jan 24, 2005
Messages
4
Damon, thank you for the fast response. I’ll try to give you a better picture of what I am trying to do. I have a spreadsheet that pulls monthly data from a lookup spreadsheet. A printed page will only fit 6 months worth of data (Jan – July), so I have a second group of rows with the remainder of the year directly below the first group (July – Dec). I have accidentally destroyed all of the formulas associated with the second group of months by selecting entire columns and copying and pasting special as values (My reporting convention is to “freeze” a month’s data once that month has passed). Now I must go back and use the original excel report template and bring in the former month’s values. The problem is, given the massive amount of data, I would prefer to continue to select groups of columns and copy and paste special as values. I just need to find some way to select only visible rows, and then paste to only visible rows in an identical file, thus preserving the hidden rows with formulas for the second half of the year.
Hopefully this make some sense, here is a brief description of my report layout:
Rows 1-14 ; Jan – June; intent: copy and paste special values from a dif. file
Rows 15-22 Hidden; July – Dec; intent is to preserve existing and not paste
Rows 23-32; Jan – June; intent: copy and paste special values from a dif. file
Rows 33-40; Hidden; July – Dec; intent is to preserve existing and not paste
Rows 41-50; Jan – June; intent: copy and paste special values from a dif. file
Rows 51-59; Hidden; July – Dec; intent is to preserve existing and not paste
Rows 60-67; Jan – June; intent: copy and paste special values from a dif. file
Rows 68-73; Hidden; July – Dec; intent is to preserve existing and not paste
And you probably get the idea from here, this pattern just continually repeats. Thank you again for your help. It is greatly appreciated.

Bryce
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Bryce,

Here is some code to try out. Let me know if this is what you wanted:

Dim A() As Variant

Sub CopyVisible()
ReDim A(Selection.Rows.Count, Selection.Columns.Count)
A = Selection
End Sub

Sub PasteVisible()
Dim iRow As Long 'row index in A array
Dim iCol As Integer 'column index in A array
Dim Row As Range

iRow = 1

For Each Row In Selection.Rows
If iRow > UBound(A, 1) Then Exit For
If Not Row.Hidden Then
For iCol = 1 To UBound(A, 2)
Selection(iRow, iCol) = A(iRow, iCol)
Next iCol
End If
iRow = iRow + 1
Next Row

End Sub



This code should be placed in a standard macro module. To do this go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste the code into the Code pane. I would also suggest assigning keyboard shortcuts to each, and recommend you use Ctrl-Shift-C for the CopyVisible macro and Ctrl-Shift-V for the PasteVisible macro. That way the shortcuts will be the same as a standard copy and paste except for the Shift. You can assign shortcuts via Alt-TMM, select the desired macro, and click the Options button.

The paste macro pastes only values. It does not actually look at whether rows are hidden or not on the source sheet, but simply does not paste the rows that would otherwise be pasted into the hidden rows of the destination sheet. And indicentally, when you do the CopyVisible the source range will not be "boxed" with marching dashed lines (since it is not a normal copy to the clipboard, but a copy to a VBA array), so you must take it on faith that the copy has occurred.

Damon
 

Forum statistics

Threads
1,147,845
Messages
5,743,513
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top