Hello,

I'm trying to transpose clustered data in a cells which values are
separated by this symbol "ý". Sample values in a cell is
"1ý2ý3ý4ý...so on and so forth" I was able to successfully
separate the values to be rows using the ý as delimeter... everything
works great if the range I selected is A1:B1 or B2:C2... transpose data
are from left to right... great.. but when I started have range like
A1:B2.. when I transpose using the code below, it still transfers the
data from left to right instead of putting the A2:B2 on the last
populated row from A1:B1...Is there a way to detect row changed on a
selected range?

This transpose data to this
A1 but when it process range A2 it still left to right instead of
moving from the last row on A1
1
2
3
4

Sub BOM_Multiple()
Dim xlapp As Excel.Application
Dim xlwkb As Excel.Workbook
Dim xlsht As Excel.Sheets

Set xlapp = Excel.Application

Dim sArray() As String
Dim i As Long, last As Long
Dim n As Integer
Dim r As Range
Dim val As String

Start:
val = InputBox("Enter the range of data to be transposed?")
If InStr(val, ":") = 0 Then
MsgBox "Please enter a valid range. E.g. A1:B3."
GoTo Start
End If

n = 1
For Each r In Sheets(5).Range(val).Cells
'Multiple cell ranges
sArray = Split(r & "ý", "ý")
With xlapp
.ActiveWorkbook.Worksheets(4).Select
last = UBound(sArray) - 1
i = 0
Do Until i last
Cells(i + 1, n).FormulaR1C1 = CStr(sArray(i))
i = i + 1
Loop
.ActiveWorkbook.Worksheets(5).Select
End With
n = n + 1
Next r

Set xlapp = Nothing

End Sub