CODEWITHSUNDEEP
vbaexceltranspose
havishmad
havishmad

Reputation: 29

Flash Fill in Excel result in columns - and not in rows

I want Flash Fill results in columns.

Data is like this:

enter image description here

Could VBA help here? The VBA could transpose the data in one row, Flash Fill that array, and then transpose it back in the next row.

Upvotes: 0

Views: 662

Answers (1)

urdearboy
urdearboy

Reputation: 14580

This is dynamic by length of header and string splits by -

Option Explicit

Sub FlashFill()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim MyCell As Range, StringRange As Range, Arr, i, lcol

Set StringRange = ws.Range(ws.Cells(1, 1), ws.Cells(1, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column))

For Each MyCell In StringRange
    Arr = Split(MyCell, "-")
        For i = LBound(Arr) To UBound(Arr)
            MyCell.Offset(i + 1) = Arr(i)
        Next i
    Arr = ""
Next MyCell

End Sub

Resulting in the lyrics of the worst song ever written output looking like so:

enter image description here

Upvotes: 2

Related Questions