Excel 2003 is my operating platform.

I have data in a row of my spreadsheet. A1:Z1 for example. I'd like to
concatenate it in VBA Code separated by a tilde "~" and have VBA put the
result in cell A10.

The code snippet below would suffice, I assume. However, it seems terribly
inefficient.

Range("A10").Value = Range("A1") & "~" & Range("B1")
........................... & "~" & Range("AZ1")

Can somebody provide guidance to make my code efficient.

sub joiner()

Dim s as string
s=Range("a1").value
Dim i as integer
for i = 1 to 26
s = s & "~" & range("a1").offset(0,i).value
next i

range("a10").value = s

end sub
"shortstop" wrote:

> Excel 2003 is my operating platform.
>
> I have data in a row of my spreadsheet. A1:Z1 for example. I'd like to
> concatenate it in VBA Code separated by a tilde "~" and have VBA put the
> result in cell A10.
>
> The code snippet below would suffice, I assume. However, it seems terribly
> inefficient.
>
> Range("A10").Value = Range("A1") & "~" & Range("B1")
> ........................... & "~" & Range("AZ1")
>
> Can somebody provide guidance to make my code efficient.
>
>
>
>
>
>

That will get AA1 as well Sam.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sam Wilson" <(E-Mail Removed)> wrote in message
news:44898DB1-E28E-46D5-AA86-(E-Mail Removed)...
> sub joiner()
>
> Dim s as string
> s=Range("a1").value
> Dim i as integer
> for i = 1 to 26
> s = s & "~" & range("a1").offset(0,i).value
> next i
>
> range("a10").value = s
>
> end sub
> "shortstop" wrote:
>
>> Excel 2003 is my operating platform.
>>
>> I have data in a row of my spreadsheet. A1:Z1 for example. I'd like to
>> concatenate it in VBA Code separated by a tilde "~" and have VBA put the
>> result in cell A10.
>>
>> The code snippet below would suffice, I assume. However, it seems
>> terribly
>> inefficient.
>>
>> Range("A10").Value = Range("A1") & "~" & Range("B1")
>> ........................... & "~" & Range("AZ1")
>>
>> Can somebody provide guidance to make my code efficient.
>>
>>
>>
>>
>>
>>

This is sloppy, but it does the job and is adaptable.

Sub testit()

Dim rngMyCells As Range

ActiveSheet.Range("A10").Value = ActiveSheet.Range("A1").Value
For Each rngMyCells In ActiveSheet.Range("B1", "Z1")
ActiveSheet.Range("A10").Value = _
ActiveSheet.Range("A10").Value & "~" & rngMyCells.Value
Next

End Sub

"shortstop" wrote:

> Excel 2003 is my operating platform.
>
> I have data in a row of my spreadsheet. A1:Z1 for example. I'd like to
> concatenate it in VBA Code separated by a tilde "~" and have VBA put the
> result in cell A10.
>
> The code snippet below would suffice, I assume. However, it seems terribly
> inefficient.
>
> Range("A10").Value = Range("A1") & "~" & Range("B1")
> ........................... & "~" & Range("AZ1")
>
> Can somebody provide guidance to make my code efficient.
>
>
>
>
>
>

Range("A10").Value =
Join(Application.Transpose(Application.Transpose(Range("A1:Z1").Value)),
"~")





"shortstop" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Excel 2003 is my operating platform.
>
> I have data in a row of my spreadsheet. A1:Z1 for example. I'd like to
> concatenate it in VBA Code separated by a tilde "~" and have VBA put the
> result in cell A10.
>
> The code snippet below would suffice, I assume. However, it seems
> terribly inefficient.
>
> Range("A10").Value = Range("A1") & "~" & Range("B1")
> .......................... & "~" & Range("AZ1")
>
> Can somebody provide guidance to make my code efficient.
>
>
>
>
>