Split comma delimited rows into multiple rows

[prab76]

I am able to split one row(A1) of comma delimited values into multiple rows with one value in each , however if have multiple rows i am having to do one by one row . can any one help

Sub splita1()
Dim X As Variant
X = Split(Range("A1").Value, ",")
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

 

[sandy666]

without vba but with PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-GB"), {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
    #"Split Column by Delimiter"[/SIZE]

source table has header: Column1