• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split Multiple Columns into One

sgmpatnaik

Active Member
Hello

Good Afternoon

i need some help that is i have some address in the columns and i want to separate into rows in one column

Example:
`
Name Address1 Address2 Address3
Name Address1 Address2 Address3

Result:

Name
Address1
Address2
Address3
Blank
Blank
Name
Address1
Address2
address3
`
Kindly suggest me

Thanks in Advance

With Regards
 
Hi,

Try this one, if your data is in column C then put this in A1 and copy down

=OFFSET($C$1,INT((ROW()-1)/6),MOD(ROW()-1,6))

Does that help for starters?
 
@oldchippy

Thanks for the above formula and quick response

but i need VB Code and my data started from A1

Thanks
 
Hi patnaik..

try this one..

Code:
Sub SpliMultiple()
Dim frw As Long, lrw As Long, fcol As Long, lcol As Long, deb As Range
Set deb = Sheets(1).Range("A1").CurrentRegion
frw = deb.Cells(1).Row
lrw = 1
fcol = deb.Cells(1, 1).Column
lcol = deb.Columns.Count + fcol - 1
 
    Application.DisplayAlerts = False
    On Error Resume Next
        Sheets("Output").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
 
Sheets.Add(, Sheets(Sheets.Count)).Name = "Output"
 
For i = 1 To deb.Rows.Count
    Range(Cells(lrw, frw), Cells(lrw + deb.Columns.Count - 1, frw)) = WorksheetFunction.Transpose(deb.Rows(i))
    lrw = Cells(lrw, fcol).Row + deb.Columns.Count + 2
Next i
End Sub
 
@Deb

Again After Long time i got suggest from Deb
great, Thanks man it's working perfect

Cheers

Have a Nice Evening

Thanks, Thanks Once again

[Solved]
 
Back
Top