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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try this:
VBA Code:
Sheet1.Application.Union(Range(i, "A"), Range(i, "C"), Range(i, "K"), Range(i, "V"), Range(i, "W")).Copy
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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