VBA Copy Non Contiguous Columns

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
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


Code:
Sub Copy()

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


Any help would be appreciated

Regards,

Humayun
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You would need to copy them separately, as the ranges need to be the same size.
Either that or you could use something like
Code:
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
 
Upvote 0
Try:
Code:
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
 
Last edited:
Upvote 0
Thanks Fluff,

Your code is working PERFECT 100%

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

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

Or a different approach will be required
 
Upvote 0
Try:
Code:
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.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top