Skip hidden rows while pasting data.. 2

[inahs2k]

Hi all,

I need some help regarding an issue that I have run into.

Say I have copied a range of cells and I want to paste it in another range but the catch is: Is it possible to skip some hidden rows in the destination range when pasting the copied data. I tried looking at the paste special function but I could not find anything that would skip the hidden rows while pasting. I dont know if it is even possible.

Any help in this regard would be most welcome.

Thanks in advance and have a nice day.
Shahnaz.

 

[rdodge]

Via the tools that's available on the spreadsheet side, this is not possible. However, if you know VBA, you can do it.

Why it's not really possible on the spreadsheet side without using VBA:

If you select a range and then copy

Select another range, but then was to only select the visible cells (which can be done by pressing F5, then Special, then Visible cells only), you are more than likely to run into an error message stating to the effect that the paste range is not the same shape as the copy range, when you attempt to paste into the selected range (after doing the special goto)

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[inahs2k]

Hi Mr. Dodge,

Thanks for your reply. I do know VBA, but I still dont know how to do what I want to do. I tried your suggestion on the spread sheet side and it did give me an error saying that the copy area and the paste area are not the same (and it did not paste the data).

But actually I will be doing it from the VBA side only. I will open a certain workbook based on the users choice (the users choice determines whether I hide some of the rows or not), copy a certain range of cells and then in the original workbook, I will paste the values (now this is where I might have to skip the hidden rows). So if you can help me with the VBA way of doing this, that would be great.

If you want, I can post this in the VBA Forum (but I thought I would get it to work on the spread sheet side and then record a macro and use it, so I posted it in the Office forum).

Once again, thanks in advance.
Shahnaz.

 

[rdodge]

Now only if I could get the code to figure out what is the range that is being copied as I been looking all through the various things in Excel (Help File, Online, and the Watch Window with the Application Object)

I was thinking of getting this information from the Clipboard object (I.e. Office Clipboard since that's what Excel uses anyhow for copying and pasting), but I'm not finding it.

At this point, I'm guessing that you are only having hidden rows (like filters), but if that's not the case, it can be very complex.

On the other hand, you will use the Selection property/object (Excel help file calls it property even though it's used like an object).

If Selection.Rows.Count > 1 Or Selection.Columns.Count > 1 Then
Selection.SpecialCells(xlCellTypeVisible).Select
Else
'Find out how many rows you need to copy to, which then you can paste one row at a time and when a row is shown as hidden, skip that row.
End If
If Selection.Areas.Count > 1 Then
Else
End If

That was the basic thought process I was having. I realize there isn't a lot to go on here, but if nothing else, you could past into a worksheet other than the one you actually plan on getting, then get the info that way with regards to the range info.

Then you could try going from row to row one at a time.


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[dcompto]

Select the range
Press Alt+;
Ctrl-C
Then paste

 

[rdodge]

That's one shortcut I didn't know about dispite that I know excel pretty well inside out, but then there's still always something else in there that's hiding and to learn it.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[inahs2k]

Hi dcompto,

I think there is a slight misunderstanding here.

I do not want to skip the hidden cells while copying but when I am pasting. I think that the procedure that you suggested works when you want to skip hidden cells before you copy a range containing those hidden cells. But what I want to do is copy a normal range of values and then when pasting those into another range containing the hidden rows I want to skip those hidden rows.

To make it more clear, say I am copying a range of values B1:B15 (there are no hidden rows in this range) from Sheet1. Now I have another sheet, Sheet2 in which say rows 10 thru 15 are hidden. So if I select D1 on Sheet2 and paste the copied range, then range D115 will be filled with the copied values i.e. the hidden range of D1015 also got filled with the values. Now what I want is that instead of the hidden range of D1015 being filled up, I want to skip that range and fill up D1620. So the range D1015 should be skipped when pasting the values.

I hope I have made it clear enough. I am really thankful for your time, but I guess that is not what I am looking for. I would greatly appreciate any help in this regard.

Thanks,
Shahnaz.

 

[rdodge]

That's what I thought you meant, but the issue that I'm having to find a solution to that is, how to get the copy range (SourceRange).

Either A, get it when it's copied (don't really know of an Event that will work on a consistent basis for detect that action via the different ways of copying). Thought about the KeyDown and MouseUp Events, but even then, would that capture when someone uses the mouse on the Edit Menu?

Or B, find out what the Copy range is from the Office Clipboard (haven't been able to locate the object that's needed in VBA to get that information). Tried using the DataObject with the GetFromClipboard Method, but that didn't work, as I learned, that is for user forms.

If attempt to paste into Range A1 of an empty worksheet, that can cause some potential issues (such as reference errors within formulae in the pasted worksheet range), but at least we would know the size of the range, but still wouldn't know which range it's from.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[ladyck3]

dcompto...

Cool Beans... gave you a star.. may not be what inahs2k is looking for but it sure is a short cut to what I do when wanting to copy visible cells only....

I do the Edit > Go To > Special > Visible Cells Only,
Then Copy
Then Page...

ALT + ; is WONDERFUL..

I should just sit down with the bazillion shortcut keys reference chart and memorize some day!

Thank!!!

Laurie
PS. Trying to give you a star but its giving me fits.. probably our network..but I'll keep trying

LadyCK3