Pasting Into Visible Cells Only

[Zach Boyer]

Up front, I should explain that I am working on a Mac and originally downloaded the Microsoft Office 2011 version which included Excel. I, however, save the workbook in question in the Excel 97-2004 (.xls) format. For the life of me I can't figure out how to paste into the visible cells only but it's absolutely crucial that I be able to do so. I've watched YouTube videos, but I think many of them apply to newer versions of Excel to which I do not have access. I've scoured these very forums and have yet to find anything definitive. I did, however, figure out how to COPY from visible cells only … and yet when I apply the same technique to pasting—highlighting the section you want, using the Mac shortcut (command + shift + Z) and then pressing paste—it fails. It's also very crucial to keep this data in its original order and therefore any technique using "sorting" could prove problematic. Disclaimer: I also am very new to Excel overall and basically need everything said in complete layman's terms … otherwise I'll be further confused. If anybody out there can hold my hand and walk me through this, I'd be very appreciative of that. Thank you.

[newdoverman]

It isn't clear to me what you are trying to do. In the picture below, I hid rows 2 and 3 and copied visible cells only in column A and then Pasted into column E while the rows were still hidden. When the rows were unhidden this is the result. Is this what you are wanting? The cells with the dotted borders are what were copied with "Visible Cells Only".
Capture.JPG

[Zach Boyer]

Thank you for the quick response. I am trying to copy something from a different sheet and paste it into a filtered column. So essentially, yes, I think I am trying to achieve what you accomplished in the screenshot. Ty.

[Zach Boyer]

Or: In order to get the data I want to copy I need to filter my worksheet. I AM ABLE to copy it. But I am not simultaneously looking at the column I copied from and the one I want to paste into … like it seems you are in the screenshot. In order to get the section I want to paste into, I need to filter again.

[newdoverman]

Select the filtered range, Go to Find and Select, Go To Special, Visible Cells Only, OK, then copy. Go to the destination and Paste. Only the contents of the visible cells will be pasted in the destination.

If you are pasting into a filtered column, that will not work as the cells filtered out in the destination will be filled with the copied data from the source.

In the screen shot that I gave you, I first hid rows 2 and 3 and copied. I then pasted into column E (rows 2 and 3 still hidden) then I unhid rows 2 and 3 and you can see that the rows that were hidden were filled with the copied data even though they were hidden at the time of pasting.

[Zach Boyer]

"If you are pasting into a filtered column, that will not work as the cells filtered out in the destination will be filled with the copied data from the source."

… Idk exactly what you mean here. But I AM pasting into a filtered section … that's kind of my whole point. Can you still help me? Ty.

[Zach Boyer]

If I could sort the data, quickly paste what I need to, then unsort it and thus retain its original order, that would solve my problem too.

[newdoverman]

Add a column with consecutive numbers to your data (before sorting) and include the column when sorting. When you want to return the data to its original order sort on the column of consecutive numbers.

[Zach Boyer]

Alright … I know how to insert a new column but how do I insert the consecutive numbers? I have a lot of rows and am not prepared to do so manually. Sorry, I'm a complete novice here. Thank you.

[newdoverman]

Enter a 1 in the first cell even with the data start. In the cell under (A3) enter for example =a2+1 (if A2 is even with the first row of data and right next to the data...no blank column). Select A3 and double click the fill handle in the lower right of the cell. This will fill the column to the bottom of the data with consecutive numbers. Select the column and copy then with the column still selected Paste Values. This will leave the values in the column but the formulae that produced the numbers will be gone.