Paste skipping hidden row

[wallstudio]

I need help in doing some special paste.

I need to copy a regular range and paste it to range with hidden row in between. I want the result to skip the hidden row.

To make it clear, source is A1 to C3. Row 10 is hidden. When I paste it to A9, data will be pasted into Row 9, 11 and 12, leaving row 10 untouched.

Is there a way to do this? Thanks a lot.

 

[AlphaFrog]

This copies A1:C3 (rngSource) one row at a time to the next Visible rows starting at A9 (rngDest) on down while skipping hidden rows.

Sub Paste_to_Visible_Rows()
    
    Dim rngSource As Range, rngDest As Range
    Dim i As Long, r As Long
    
    Set rngSource = Range("A1:C3")
    Set rngDest = Range("A9")
    
    For i = 1 To rngSource.Rows.Count
        Do Until Not rngDest.Offset(r).Rows.Hidden
            r = r + 1
        Loop
        rngSource.Rows(i).Copy Destination:=rngDest.Offset(r)
        r = r + 1
    Next i
    
End Sub

 

[wallstudio]

Thanks for you reply. I still have a question. If I am not copying from range in the workbook, but from another software. That means my source range is in the clipboard. can it be done in that way?

 

[AlphaFrog]

There may be a way to do it directly from the clipboard, but it would very much matter on what exactly is the data structure on the clipboard. Is it comma delimited text, HTML code,... or what? What is the source data software? The clipboard data could be parsed to an array variable and then pasted to the destination range. Parsing the data from the clipboard is a bit of a pain coding-wise.

The simplest way, albeit less elegant, would be to paste the clipboard data to a temporary location (a blank worksheet maybe) that has all visible rows. Then use the above code to copy\paste the temporary data to the final destination that has the hidden rows.

 

[wallstudio]

I don't know what's the format of the data. The software is called Patternz. It a little software to find stock patterns. I can easily paste it to excel without any problem.

What I am trying to do is to paste the result to excel and base on the result to grab the current price of stocks from yahoo by a web query . The query will return error if there are more than certain numbers of stock. In order to get hundreds of stock prices, I have to add more than 1 query. And at the last row of the query, it returns no data. Therefore, I must leave a blank row at the end of each query when pasting the references. And I think hiding the row is the way to do it.

Anyway, your suggestion of pasting to a temp location still works. Thank you very much.