CODEWITHSUNDEEP
vbaexcel
owner
owner

Reputation: 743

Define two ranges and then join them

I am currently wondering on how to select as a range in VBA, ranges of values, and hope someone could assist on the below piece of code. 

eoline = 50
first = 5
For i = 0 To eoline
multi = 2 * i + 1
a = Cells(first + 1, 1).Select
s1 = Range(Selection, Selection.End(xlDown)).select %select 1st range of values
b = Cells(first + 1, 2 * (i + 1)).Select
s2 = Range(Selection, Selection.End(xlDown)).select %select 2nd range of values
next i

Actually I'd like to select and copy s1 & s2, and I've unsuccessfully tried 

myval = Union(s1, s2).select
selection.copy

Thanks

Upvotes: 2

Views: 88

Answers (2)

owner
owner

Reputation: 743

I found the solution. Here is the complete solution to my problem:

Dim s1, s2, myval As Range
eoline = 50
first = 5

multi = 2 * i + 1
a = Cells(first + 1, 1).Select
Set s1 = Range(Selection, Selection.End(xlDown))
b = Cells(first + 1, 2 * (i + 1)).Select
Set s2 = Range(Selection, Selection.End(xlDown))
Set myval = Union(s1, s2)
myval.Select

Upvotes: 0

brettdj
brettdj

Reputation: 55702

Something like this

  1. Use Set when you create your ranges
  2. Don't use Select.
  3. When setting ranges look bottom up rather than selecting down (which will stop at a data gap if you have one)

code

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

Dim lngCnt As Long
Dim lngCn2 As Long

lngCnt = 5
lngCnt2 = 2


Set rng1 = Range(Cells(5, 1), Cells(Rows.Count, 1).End(xlUp))

Set rng1 = Range(Cells(lngCnt + 1, 1), Cells(Rows.Count, 1).End(xlUp))
Set rng2 = Range(Cells(lngCnt + 1, 2 * (lngCnt2 + 1)), Cells(Rows.Count, 2 * (lngCnt2 + 1)).End(xlUp))

Set rng3 = Union(rng1, rng2)

Upvotes: 2

Related Questions