PDA

View Full Version : Solved: moving texts separated by a dash (-) for the bottom row

marreco
11-02-2012, 08:55 AM
Hi.

I need to move (via VBA), texts separated by a dash (-) to the bottom line.

However column "B" must follow the strokes according to the number of rows.
eg
WORKSHEET "BEFORE"
Column "A" ---------------- Column "B"
Itapemerim - Vila Velha Espirito Santo

WORKSHEET "RESULT"
Column "A"--- Column "B"
Itapemerim Espirito Santo
Vila Velha Espirito Santo
In this example have only two pieces, more cases can have 10 or more and thus have 10 or more lines depending on the case.

Vaje my attachment

patel
11-02-2012, 09:44 AM
Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For j = LR To 2 Step -1
city = Cells(j, 1)
State = Cells(j, 2)
If InStr(city, "-") > 0 Then
arr = Split(city, " - ")
numrows = UBound(arr)
Cells(j + 1, 1).Resize(numrows, 1).EntireRow.Insert
Cells(j + 1, 1).Resize(numrows, 2).Value = Cells(j, 1).Resize(1, 2).Value
For i = 0 To numrows
Cells(j + i, 1).Value = arr(i)
Next
End If
Next
End Sub

p45cal
11-03-2012, 03:11 AM
…or instead of: Cells(j + 1, 1).Resize(numrows, 2).Value = Cells(j, 1).Resize(1, 2).Value
For i = 0 To numrows
Cells(j + i, 1).Value = arr(i)
Next
you could have:
Cells(j + 1, 2).Resize(numrows).Value = Cells(j, 2).value
Cells(j, 1).Resize(numrows + 1) = Application.Transpose(arr)

patel
11-03-2012, 09:23 AM
very good, maybe the code I attached is your code ?

marreco
11-04-2012, 09:13 AM
Hi.

Thank you very much!!