Solved: extract values in a range

[surya prakash]

Hi,

I have values 1,2,3,4,5 in (a1 to a5) cells; I am wondering if these values can be extracted using VBA?

thanks

[GTO]

Greetings Surya,

They can, and through several ways. Could you decribe what the goal is?

Mark

[surya prakash]

Greeting Mark,
I have following data in Excel sheet in a1 to a5 fields
1
2
3
4
5

I have function as shown below; the idea is to append them in the following format
1,2,3,4,5

[VBA]

Function myJoinList(ByVal myRange As Range) As String

[/VBA]

thanks

[GTO]

Hi again,

As shown, you could use Transpose and Join I believe. In case you may need the vals to come from, let's say, A1:F1 sometime, I think this should grab either. Sorry for the delay - "fuzzy brains" here couldn't get cols/rows in the correct order...

In a new blank WB:

On Sheet1, put some vals in A1:F5

In a Standard Module:
[vba]
Sub test()
MsgBox myJoinList(ThisWorkbook.Worksheets("Sheet1").Range("A1:A5"))
MsgBox myJoinList(ThisWorkbook.Worksheets("Sheet1").Range("A1:F1"))
MsgBox myJoinList(ThisWorkbook.Worksheets("Sheet1").Range("A1:F5"))
End Sub

Function myJoinList(ByVal myRange As Range) As Variant
Dim aryIn, aryOut
Dim x As Long, y As Long

aryIn = myRange.Value
ReDim aryOut(0 To 0)

For x = LBound(aryIn, 1) To UBound(aryIn, 1)
For y = LBound(aryIn, 2) To UBound(aryIn, 2)
ReDim Preserve aryOut(1 To UBound(aryOut) + 1)
aryOut(UBound(aryOut)) = aryIn(x, y)
Next
Next

myJoinList = Join(aryOut, ",")
End Function
[/vba]

Hope that helps,

Mark

[surya prakash]

Hi Mark,
Many thanks for your prompt response.
The solution seems to be working; will revert back to you shortly

[surya prakash]

Hi mark, Many thanks for resolving my post..