LinkBack URL
About LinkBacks
Hi all,
I'm new to VBA and I'm looking for a macro which would do the following:
I have this:
555545, 4555 42225, 44 4444, 5, 4445, 88, 986
and I want this:
555545 4555 42225 44 4444 5 4445 88 986
Many thanks in advance!
Hi all,
I'm new to VBA and I'm looking for a macro which would do the following:
I have this:
555545, 4555 42225, 44 4444, 5, 4445, 88, 986
and I want this:
555545 4555 42225 44 4444 5 4445 88 986
Many thanks in advance!
Assuming your list starts in A1:
Sub rose9812in() Application.ScreenUpdating = False i = Cells(Rows.Count, "A").End(xlUp).Row Do While i > 0 temp = Split(Cells(i, 1).Value, ",") For j = UBound(temp) To 0 Step -1 Rows(i).Insert Cells(i, 1).Value = temp(j) Next Rows(i + UBound(temp) + 1).EntireRow.Delete i = i - 1 Loop Application.ScreenUpdating = True End Sub
I'm interested in starting a career working with VBA, if anyone knows of any opportunities!
This worked perfectly! Thank you very much. What if the list doesn't start from A1 and starts from A5?
Do While i > 4
Thanks again for your prompt response. Another question related to this topic. What if I would like to do this for multiple columns, A, B & C all at once?
Can you post a before and after of the start and end results you want like you did in your first post?
FWIW:
Sub rose9812in() Dim i As Long, x As Long With Application .DisplayAlerts = False .ScreenUpdating = False .Calculation = xlManual End With Range("A2:A" & Range("A" & Rows.Count).End(3).row).TextToColumns Range("A2"), xlDelimited, xlDoubleQuote, False, True, False, True, False, True For i = Range("A" & Rows.Count).End(3).row To 2 Step -1 x = Cells(i, "A").End(xlToRight).Column Cells(i + 1, "A").Resize(x).Insert xlDown Range(Cells(i, "B"), Cells(i, x)).Copy Cells(i + 1, "A").PasteSpecial Transpose:=True Range(Cells(i, "B"), Cells(i, x)).Clear Next i Range("A2:A" & Range("A" & Rows.Count).End(3).row).SpecialCells(4).Delete With Application .DisplayAlerts = True .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub
Thanks but this is not what I was looking for. The Macro provided by John H. Davis moved all the values to Column A.
This is what I have:
115, 54 454, 245, 5454 545 45, 548, 855 45 10, 11 45, 5 2 3, 4
And this is what I want:
115 454 545 54 245 10 45 5454 11 548 45 3 855 2 4 45 5
Try this:
Sub rose() Dim rng As Variant, vSplit As Variant Dim i As Integer For i = 1 To 3 'column A,B,C rng = Join(Application.Transpose(Range(Cells(5, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i))), ",") vSplit = Split(rng, ",") Cells(5, i).Resize(UBound(vSplit) + 1).Value = Application.Transpose(vSplit) Next i End Sub
If you are happy with my response please click the * in the lower left of my post.
This is not working for me. I might have to edit it based on the way my data is set up. Could you explain what Cells(5,i) is referring to?
Thanks!
@stnkynts: Beautiful approach, put me to shame this time. Nice job.
@walrus. Thank you kindly
@rose. In general terms Cell(5, i) is a range which is further defined by the integer i, which based upon the loop is either 1, 2, or 3. When i is 1 the range will be Cells(5, 1) otherwise known as A5.
@stnkynts - Thanks for your explanation. But unfortunately, the code is not working for me. It is only inputting one value for cell A5; and all other cells (A1:C3) remains the same.
@stnkynts and/or @walruseggman - Can you write similar to what @walruseggman had provided earlier?
Thanks!
You previously indicated you wanted it to start at row 5 and so I coded it to start at row 5. If you want it to start at row 1 you would need to change the code to reflect this:It is only inputting one value for cell A5; and all other cells (A1:C3) remains the same.
Sub rose() Dim rng As Variant, vSplit As Variant Dim i As Integer For i = 1 To 3 'column A,B,C rng = Join(Application.Transpose(Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i))), ",") vSplit = Split(rng, ",") Cells(1, i).Resize(UBound(vSplit) + 1).Value = Application.Transpose(vSplit) Next i End Sub
@stnkynts - This worked perfectly! Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks