Do not paste in hidden cells

[GIORGOS]

I have a spreadsheet with 30 rows I hide 20 that are not in order and I want
to copy and paste data from another spreadsheet only in the visible cells.
But when I copy and paste excel paste from the cell I select and continues
pasting also at the hidden cells so I loose the order. Is there a way to
avoid pasting data in the hidden cells?

 

[Dave Peterson]

Maybe you could sort your range so the visible cells are together. Then paste
over those visible rows. Then sort the data back the way you want???

 

[GIORGOS]

It worked! Thanks a lot!

Maybe you could sort your range so the visible cells are together. Then paste
over those visible rows. Then sort the data back the way you want???

 

[Arun]

Funny. Mr. Peterson had a better answer to this problem back on a post dated
7/06/06, just in reverse (how do you COPY a selection while ignoring hidden
rows). Try this:

Select your copy range
edit|copy
Select your filtered target range
edit|goto special|Visible cells only
edit|paste

 

[Dave Peterson]

Did that actually paste into the visible cells (and leave the hidden cells
alone) for you?

 

[Arun]

Sure did. Did it work for you?

PS: Of the hundred or so posts I've seen from you, that's the first time I
think I've seen you ASK a question on this site. Thanks for all your
contributions.

 

[Dave Peterson]

I was surprised that it worked for you.

I tested using xl2003.

I put 1,2,1,2,1,2,1,2,1,2,... in A1:A20 of sheet1
I put a,b,c,d,e,f,... in A1:A14 of sheet2

I copied A1:A14 of sheet2.
I filtered to show the 2's in sheet1
I selected that filtered range and did the Edit|goto|special|visible cells
and then (with A2 the active cell in the selection), I did edit|paste

And when I showed all the data, it wasn't just the visible cells that got pasted
over.

Sure did. Did it work for you?

PS: Of the hundred or so posts I've seen from you, that's the first time I
think I've seen you ASK a question on this site. Thanks for all your
contributions.

 

[Arun]

You're right, that doesn't work. In the application I was using only
required posting single row repeatedly into the filtered range.

 

[Vikas]

Is there a solution then if sorting the list is not an option?

You're right, that doesn't work. In the application I was using only
required posting single row repeatedly into the filtered range.

 

[Arun]

Try this.

* Filter it the way you want.
* In an unused column adjacent to your list as part of your list and paste
in the any number or letter using the method described on my first post below
(this method does work if your copy range is just a single cell or row).
* Turn off the list function and use this new column as your first sort
criteria.
This will put all the rows you want to paste in adjacent rows for a standard
copy paste.