Transpose code has ceased to transpose

[Howard]

I arcived this code from this forum, I believe. The original data in a single cell was delimited by a / and this code transposed the cell contents from B1 to a list in column A, sans the /. I substituted a comma for the / and it worked just fine on my test cell.

A few minutes ago I tried to use it on comma delimited stuff in cell B1 andnow all I get is a long string of commas... ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, etc, in cell B1, nothing in column A.

Anybody know what the heck is going on.
Excel 2010 and code is in Sheet1 module.

Option Explicit

Sub SuperTranspose()
Range("B1") = Join(Application.Transpose(Range(Range("A1"), _
Range("A1").End(xlDown))), ", ") '" / ")
End Sub

Regards,
Howard

 

[Ben McClave]

Howard,

I became a bit confused when trying to follow what the macro is intended todo. It sounds like you want it to take a single string from cell B1 and split it out to its component pieces in column A. The macro you posted, however, does the opposite. It takes the data in column A and concatenates it with a delimiter in cell B1. Thus, with no data in column A, the result ofthe macro will be a string of commas.

After trying out the macro you provided, I reworked it to do the opposite (take a value in cell B1 and transpose it to column A). Both versions are copied below. Hopefully one of them will work for your needs.

Option Explicit

Sub SuperSplit()
'copies elements in cell B1 into column A
Dim vArray As Variant
Dim x As Long

vArray = Split(Application.Transpose(Range("B1")), ", ") '" / ")

For x = 0 To UBound(vArray)
Range("A1").Offset(x, 0).Value = vArray(x)
Next

End Sub

Sub SuperJoin()
'Joins column A elements into a single string in cell B1
Range("B1") = Join(Application.Transpose(Range(Range("A1"), _
Range("A" & Rows.Count).End(xlUp))), ", ") '" / ")
End Sub

 

[Howard]

I arcived this code from this forum, I believe. The original data in a single cell was delimited by a / and this code transposed the cell contents from B1 to a list in column A, sans the /. I substituted a comma for the /and it worked just fine on my test cell.



A few minutes ago I tried to use it on comma delimited stuff in cell B1 and now all I get is a long string of commas... ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, etc, in cell B1, nothing in column A.



Anybody know what the heck is going on.

Excel 2010 and code is in Sheet1 module.



Option Explicit



Sub SuperTranspose()

Range("B1") = Join(Application.Transpose(Range(Range("A1"), _

Range("A1").End(xlDown))), ", ") '" / ")

End Sub



Regards,

Howard

Hi Ben,

You are completely correct, I completely forgot and missread which way the transpose was coded to go. It works just fine when used correctly.

Thanks for rewite of the one going the other way, very much appreciated.

Regards,
Howard

 

[Ben McClave]

Glad to help.