LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 200
Default Disconnected Range to Variant

Here's another way, which, too, depends on functions from the web site;
I didn't take the trouble to declare variables, but of course you should:

Sub abtest1()
Dim arr1()
ReDim arr1(1 To 3, 1 To 1)
With ThisWorkbook.Worksheets(1)
arr1 = .Range(.Cells(2, 1), .Cells(4, 2)).Value
arr2 = .Range(.Cells(2, 6), .Cells(4, 6)).Value
End With
'Add a second column of empty values to arr1
ReDim Preserve arr1(1 To 3, 1 To 2)
'Replace the second column of arr1 with the values from arr2
ReplaceSubArray arr1, arr2, 1, 2
End Sub

If the code is in a general module I would replace the 3rd thru 6th
lines with the following, which better suits my taste:

Set rng = ThisWorkbook.Worksheets(1).Range("A1")
arr1 = Range(rng(2, 1), rng(4, 2))
arr2 = Range(rng(2, 6), rng(4, 6))

I'm not a fan of the Cells Property, and in addition, the above avoids
the need for qualification if Worksheets(1) is not the active sheet.

Alan Beban


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I assign range to variant and use Mike H[_2_] Excel Discussion (Misc queries) 7 June 7th 07 01:40 AM
Assigning range to variant vezerid Excel Programming 2 March 12th 07 04:46 PM
Assigning range to variant vezerid Excel Programming 2 March 12th 07 04:45 PM
How to define a Range with variant? Yiu Choi Fan Excel Programming 6 July 12th 04 04:41 PM
Best way to paste a variant array into a range? AnneB Excel Programming 2 April 28th 04 09:57 PM


All times are GMT +1. The time now is 02:49 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"