get visible cells range to another range

[Jimijames]

Dears,

First of all thank You for Your help I got from You till now (i have been visiting this forum since a long time but registered only now as i am unable to solve one problem). !


Here is my question to You.

I have some data in Range("F1:J364"), it is filtered (i exclude all #N/A`s) - for now lets say selection is ("F1:J16,F20:J36,F40:J130")
I would like to have this filtered data in another place which is Sheet1 - data should start in Range("A1")
(I do not want to use, select and paste as this makes my code run slowly)

Here is what i came up with but is only pastes data to first hidden row so I get data only for Range("F1:J16") rest comes up as #N/A.

........
Dim Rng As Range
Set Rng = Selection.SpecialCells(xlCellTypeVisible)

Sheets("Sheet1").Select
Range("A1:E364") = Rng.Value


Also I know that if selection will be less than 364 tan remaining cell will be #N/A - but i believe i can overcome this on my own
.
Kindly asking for Your help.

THank You
Michal

 

[Andrew Poulsom]

Welcome to MrExcel.

Did you try Copy/Paste? The destination should be a single cell.

 

[Jimijames]

Dear Andrew,

I am so glad to see You answering to my question for the firs time Thanks

As i wrote i do not want to use copy paste as this slows my code down.
I had it that way but when I got a piece of data using below code, which is working fine, I was nicely surprised by speed increase:

Set Rng1 = Range("D1:E3")
Range("A1:B3") = Rng1.Value

Destination is in another Workbook and this workbook is an attachement to e-mail message.
I am looking for any speed-up posibilities and this one looks promissing.
But any other fast solution would be good.

Kind regards
Michal

 

[Andrew Poulsom]

You can't use Value = Value with a non-contiguous range, sorry.

 

[Jimijames]

Thanks Andrew, i can stóp wasting my time now.