Split Multiple Columns into One

[sgmpatnaik]

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

 

[oldchippy]

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?

 

[sgmpatnaik]

@oldchippy

Thanks for the above formula and quick response

but i need VB Code and my data started from A1

Thanks

 

[Debraj]

Hi patnaik..

try this one..

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

 

[sgmpatnaik]

@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]

 

[Debraj]

You welcome..

 

[sgmpatnaik]

Deb

if i want to extend the Column then

 

[Debraj]

as expected.. i know.. you will ask for it..
will work better..