Selecting Non-Contiguous/Non-Adjacent Cells in a Range

TylerCross

New Member
Joined
Jan 5, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone who reads this,
I am currently stuck on how to complete this task. I currently coding a VLOOKUP macro. I am only needing to select a columns out of my data which are A, C ,K, V and W. Below I posted my code that I have, the highlighted section is where the problem is occurring. I have tried multiple things but I seem to not figure out. Any help is appreciated.
Sub Transfer()
Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String
lastrow1 = Sheet1.Range("K" & Rows.Count).End(xlUp).Row


For i = 2 To lastrow1
myname = Sheet1.Cells(i, "K").Value

Sheet3.Activate
lastrow2 = Sheet3.Range("D" & Rows.Count).End(xlUp).Row

For j = 2 To lastrow2

If Sheet3.Cells(j, "D").Value = myname Then
Sheet1.Activate
Sheet1.Union(Range(i, "A"), Range(i, "C"), Range(i, "K"), Range(i, "V"), Range(i, "W")).Copy
Sheet3.Activate
Sheet3.Range(Cells(j, "O"), Cells(j, "S")).Select
ActiveSheet.Paste
End If

Next j
Application.CutCopyMode = False
Next i
Sheet1.Activate
Sheet1.Range("A1").Select

-Tyler


End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
Sheet1.Application.Union(Range(i, "A"), Range(i, "C"), Range(i, "K"), Range(i, "V"), Range(i, "W")).Copy
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
Now if I understood exactly what you were attempting to do I maybe could write your code in another way. Using activate sheet and select is normally not needed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,140
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
With Sheet1
   Union(.Cells(i, "A"), .Cells(i, "C"), .Cells(i, "K"), .Cells(i, "V"), .Cells(i, "W")).Copy
End With
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

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
Top