without for loop

[tom taol]

the below source code work, but is not true. why?
i want to be a15=2,b15=5,a19=7,b19=8
i want to be worked using array or union method without for loop.

Sub unii()
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets(1)

Set uni = Union(sht.[a7:b7], sht.[a9:b9])
Set uni_tar = Union(sht.[a15:b15], sht.[a19:b19])
uni_tar.Value = uni.Value
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Can you do it one at a time?

sht.[a15:b15].Value = sht.[a7:b7].Value
sht.[a19:b19].Value = sht.[a9:b9].Value


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[tom taol]

oh no.....
it is example only.
if 100 line, i have to do 100 line typing..
it is not.
i want to use the union method or array.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Simon Murphy]

or by area:

uni_tar.Areas(1).Value = uni.Areas(1).Value
uni_tar.Areas(2).Value = uni.Areas(2).Value

copy paste skipping blanks might be an option

cheers
Simon

Can you do it one at a time?

sht.[a15:b15].Value = sht.[a7:b7].Value
sht.[a19:b19].Value = sht.[a9:b9].Value


--

HTH

RP
(remove nothere from the email address if mailing direct)

the below source code work, but is not true. why?
i want to be a15=2,b15=5,a19=7,b19=8
i want to be worked using array or union method without for loop.

Sub unii()
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets(1)

Set uni = Union(sht.[a7:b7], sht.[a9:b9])
Set uni_tar = Union(sht.[a15:b15], sht.[a19:b19])
uni_tar.Value = uni.Value
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tushar Mehta]

I imagine someone will prove me wrong, but if you have a hundred non-
contiguous areas to handle, there is no practical way to do this
without a loop. It almost appears to be a homework assignment. Is it?
If so, ask the professor for some tip(s) on what s/he expects.

That said, if you can treat all the non-contiguous areas as a single
contiguous area and just make it look like they are non-contiguous, you
may be able to use a XL formula that returns "" for all the cells that
should appear blank.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

Don't know why loops are taboo, so here is one

Sub unii()
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets(1)
Dim uni As Range, uni_tar As Range
Dim area
Dim i As Long

Set uni = Union(sht.[A7:B7], sht.[A9:B9])
Set uni_tar = Union(sht.[A15:B15], sht.[A19:B19])
For i = 1 To uni_tar.Areas.Count
uni_tar.Areas(i).Value = uni.Areas(i).Value
Next i
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

Bob,

a while ago Tom Ogilvy advised me that using the
[] (or Evaluate) syntax for range refencing makes code
look pretty but execute slow.

Also I found that with 'simple' unions there's more to be
gained from syntaxing...

decreasing from 6 to .9 secs (for 100k calls)


Sub SyntaxSpeed()
Dim t!, i&, u As Range
Const m = 100000
t = Timer
For i = 1 To m
Set u = Union([sheet1!a1:b2], [sheet1!d1:e2])
Next
Debug.Print (Timer - t)

t = Timer
For i = 1 To m
Set u = Union(Range("sheet1!a1:b2"), Range("sheet1!d1:e2"))
Next
Debug.Print (Timer - t)

t = Timer
For i = 1 To m
Set u = Worksheets("sheet1").Range("a1:b2,d1:e2")
Next
Debug.Print (Timer - t)

t = Timer
For i = 1 To m
Set u = Range("sheet1!a1:b2,sheet1!d1:e2")
Next
Debug.Print (Timer - t)

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Bob Phillips]

I agree, I think it doesn't even look pretty and never use it myself, but
that is what the OP used, and I was concentrating on the bit that he didn't
have.

As to the timings and 'simple' unions, it appears from the OP's previous
response that perhaps his unions will not be so simple

I also added this version to your tests, which I expected to be fastest of
all, but it was approx 50% slower than your fastest

t = Timer
With Worksheets("Sheet1")
For i = 1 To m
Set u = .Range("a1:b2,d1:e2")
Next
End With
Debug.Print (Timer - t)

Bob

Bob,

a while ago Tom Ogilvy advised me that using the
[] (or Evaluate) syntax for range refencing makes code
look pretty but execute slow.

Also I found that with 'simple' unions there's more to be
gained from syntaxing...

decreasing from 6 to .9 secs (for 100k calls)


Sub SyntaxSpeed()
Dim t!, i&, u As Range
Const m = 100000
t = Timer
For i = 1 To m
Set u = Union([sheet1!a1:b2], [sheet1!d1:e2])
Next
Debug.Print (Timer - t)

t = Timer
For i = 1 To m
Set u = Union(Range("sheet1!a1:b2"), Range("sheet1!d1:e2"))
Next
Debug.Print (Timer - t)

t = Timer
For i = 1 To m
Set u = Worksheets("sheet1").Range("a1:b2,d1:e2")
Next
Debug.Print (Timer - t)

t = Timer
For i = 1 To m
Set u = Range("sheet1!a1:b2,sheet1!d1:e2")
Next
Debug.Print (Timer - t)

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

Don't know why loops are taboo, so here is one

Sub unii()
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets(1)
Dim uni As Range, uni_tar As Range
Dim area
Dim i As Long

Set uni = Union(sht.[A7:B7], sht.[A9:B9])
Set uni_tar = Union(sht.[A15:B15], sht.[A19:B19])
For i = 1 To uni_tar.Areas.Count
uni_tar.Areas(i).Value = uni.Areas(i).Value
Next i
End Sub