Copying non-adjacent columns to other sheet

[Maraua]

Hi,
I have the following code that copies filtered data from one sheet to another. I use the copied data to populate a listbox on a userform:

    With ws        .AutoFilterMode = False
        
        Set rng = ws.Range("B8:R8" & .Cells(Rows.Count, "B").End(xlUp).row)
        rng.Copy ws1.Range("B7")
        rngdest = "Sheet2!" & .Range("B8").CurrentRegion.Address
    End With
    With ListBox1
        .RowSource = rngdest

        .ColumnCount = 17
        .ColumnWidths = "0;60;90;0;24;40;20;130;24;42;42;55;20;120;130;110;30"
    End With
    Set rng = Nothing

I have been trying to adjust this to copy multiple, non-adjacent columns.
When I use

 Set rng = ws.Range("B8:R" & ", T8:Z" & .Cells(Rows.Count, "B").End(xlUp).row)I get a runtime error 1004.

Any thoughts on how to accomplish this?
Thanks.

 

[Fluff]

Hi & welcome to MrExcel
Try

Set Rng = Ws.Range("B8:R" & .Cells(Rows.Count, "B").End(xlUp).Row & ", T8:Z" & .Cells(Rows.Count, "B").End(xlUp).Row)

 

[DanteAmor]

Try this

dim lr as long
lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = ws.Range("B8:R" & lr & ",T8:Z" & lr)

 

[Maraua]

Thanks guys,
Both work great.
I prefer Dante's. I will have 15-20 ranges to copy.
So I will end up with less code, and when I tried both the codes.

I had the impression that Dante's was a bit faster. Or is this just my imagination?

 

[Fluff]

I wouldn't expect any noticeable difference in time, although Dante's code only calculates the last row once, rather than twice

 

[Maraua]

Ok.
Any pro's or con's with either of the 2 codes?

 

[Fluff]

Not particularly, although I would tend to go with Dante's code as it's more compact & if you need to copy other ranges you can just keep using "lr", rather than recaculating it.

 

[DanteAmor]

Sorry, I was eating.


Nor do I see differences, neither in time nor in advantages, pro's: it could be that the code is more compact and less risk of making mistakes when using it several times

 

[Maraua]

Great,

again thanks for the time and effort from you both.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.