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

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
694
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.

Code:
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.

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,886
Try
Code:
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
 

Forum statistics

Threads
1,085,723
Messages
5,385,468
Members
401,956
Latest member
MadameHugo

Some videos you may like

This Week's Hot Topics

Top