Save "Range-Value" to variable?
 

Hi,

I need to extract the Range of an excel sheet into a variable.
I want to get the "A1:C10" value as a string or as a list, if there
are multiple selections. I have looked into the VBA manual of
WinXP-Excel but I can't found this topic.

VarRange = Worksheet("Sheet1").Range won't work.

If somebody knows an answer, please give me a reply.

sincerely
A. Winter

Save "Range-Value" to variable?
 

dim MyData as Variant

MyData = Range("A1:C10")

now you have an array (MyData) through which you can
loop..


Sub Test()
Dim MyData As Variant
Dim rw As Long
Dim cl As Long

MyData = Range("A1:C10")

For rw = LBound(MyData, 1) To UBound(MyData, 1)

For cl = LBound(MyData, 2) To UBound(MyData, 2)

' do something with MyData(rw,cl)
MsgBox MyData(rw, cl)
Next cl

Next rw

End Sub



Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I need to extract the Range of an excel sheet into a
variable.
I want to get the "A1:C10" value as a string or as a
list, if there
are multiple selections. I have looked into the VBA
manual of
WinXP-Excel but I can't found this topic.

VarRange = Worksheet("Sheet1").Range won't work.

If somebody knows an answer, please give me a reply.

sincerely
A. Winter
.

Save "Range-Value" to variable?
 

What do you mean by multiple selections? A1:C10 is multiple cells, but if
selected is a single area. Do you mean multiple areas (non contiguous cells
selected).

--
Regards,
Tom Ogilvy

"Andreas Winter" wrote in message
om...
Hi,

I need to extract the Range of an excel sheet into a variable.
I want to get the "A1:C10" value as a string or as a list, if there
are multiple selections. I have looked into the VBA manual of
WinXP-Excel but I can't found this topic.

VarRange = Worksheet("Sheet1").Range won't work.

If somebody knows an answer, please give me a reply.

sincerely
A. Winter

Save "Range-Value" to variable?
 

Andreas,

I may misunderstand your question, but here is my suggestion anyway:

Sub StoreRangeAddresses()
'DEclare variables
Dim i As Long
Dim arr() As String
ReDim arr(1 To Selection.Areas.Count) As String

'Store addresses in array
For i = 1 To Selection.Areas.Count
arr(i) = Selection.Areas(i).Address(False, False)
Next i

'Loop through array and display its contents
For i = 1 To UBound(arr, 1)
MsgBox arr(i)
Next i
End Sub

Ture Magnusson
Karlstad, Sweden

"Andreas Winter" wrote in message
om...
Hi,

I need to extract the Range of an excel sheet into a variable.
I want to get the "A1:C10" value as a string or as a list, if there
are multiple selections. I have looked into the VBA manual of
WinXP-Excel but I can't found this topic.

VarRange = Worksheet("Sheet1").Range won't work.

If somebody knows an answer, please give me a reply.

sincerely
A. Winter