Seeking method to copy non-contiguous columns with LastRow range method

[auto.pilot]

As the title says, I am seeking to copy a non-contiguous range of cells in columns that are identified by code using Lr (LastRow).

The following works, because only the range of B6:C37 and H6:H37 are copied.

Sub CopyNonContRange()

    
    Range("B6:C37,H6:H37").Copy

End Sub

When using Lr, this does not work... Instead, the range of B6:H37 is copied.

The last row of data in all columns is the same and their are no blank cells.

Sub CopyNonContRange2()

Dim Lr As Long
Lr = Range("B" & Rows.Count).End(xlUp).Row

    
    Range("B6:C" & Lr, "H6:H" & Lr).Copy

End Sub

How can I use both Lr and copy non-contiguous ranges?

I searched, but did not come up with anything quite like what I need.

Appreciate any thoughts.

Thanks in advance.

jim

 

[mikerickson]

Try

Dim Lr As Long
Lr = Range("B" & Rows.Count).End(xlUp).Row

With Range("6:" & Lr)
    Application.Intersect(Range("A:C, H:H").EntireColumn, .EntireRow).Copy
End With

 

[Marcelo Branco]

Maybe... (untested)

Range("B6:C" & Lr & ",H6:H" & Lr).Copy

M.

 

[auto.pilot]

Thanks!

Both of these suggestions work.

Jim