VBA Copy Non Contiguous Columns

[hrayani]

Hello Friends,

The code is copying two non-contiguous columns.

How to tell the code to look at last used cell in Columns B & D in said case i.e. from B2 to the last used cell and from D2 to the last used cell

Sub Copy()

    Range("B2:B100,D2:D100").Copy
    Range("G2").PasteSpecial Paste:=xlPasteValues
        
End Sub

Any help would be appreciated

Regards,

Humayun

 

[Fluff]

You would need to copy them separately, as the ranges need to be the same size.
Either that or you could use something like

Sub Copy()
   Dim LrB As Long, LrD As Long, lr As Long
   LrB = Range("B" & Rows.Count).End(xlUp).Row
   LrD = Range("D" & Rows.Count).End(xlUp).Row
   lr = IIf(LrB > LrD, LrB, LrD)
    Range("B2:B" & lr & ",D2:D" & lr).Copy
    Range("G2").PasteSpecial Paste:=xlPasteValues
        
End Sub

 

[JackDanIce]

Try:

Sub Copy

Dim LR as Long
LR = Application.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 4).End(xlUp).Row)

Range(Replace("B2:B@,D2:D@", "@", LR)).Copy
Range("G2").PasteSpecial Paste:=xlPasteValues

End Sub

 

[hrayani]

Thanks Fluff,

Your code is working PERFECT 100%

Sub fluff()   
   Dim LrB, LrD As Long 'modified
   Dim LR As Long
   
   LrB = Range("B" & Rows.Count).End(xlUp).Row
   LrD = Range("D" & Rows.Count).End(xlUp).Row
   
   LR = IIf(LrB > LrD, LrB, LrD)
    
    Range("B2:B" & LR & ",D2:D" & LR).Copy
    Range("G2").PasteSpecial Paste:=xlPasteValues
        
End Sub

What if more than 2 columns (Lets say 4 columns - B,D,M,O)...
How will this part of the code be managed

 LR = IIf(LrB > LrD, LrB, LrD)

Or a different approach will be required

 

[hrayani]

Try:

Sub Copy

Dim LR as Long
LR = Application.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 4).End(xlUp).Row)

Range(Replace("B2:B@,D2:D@", "@", LR)).Copy
Range("G2").PasteSpecial Paste:=xlPasteValues

End Sub

Thanks Jack - Code is working PERFECT.

 

[Fluff]

What if more than 2 columns (Lets say 4 columns - B,D,M,O)...

Use the code by JackDanIce & simply add the extra columns to the Max function.

 

[hrayani]

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback