One Entry to Multiple Rows



I have data that looks like this:
X1 | Y1 Y2 Y3 Y4
X2 | Y4 Y5 Y6 Y7

And I need to get to:
X1 | Y1
X1 | Y2
X1 | Y3
X1 | Y4
X2 | Y4
...... etc.

I can change the 2nd row's entries to more columns, but that doesn't seem to
get me much closer to the needed format (and there are thousands of lines so
I'd rather not do it manually). Any ideas?



Don Guillett

should do it. change mc to suit
Option Explicit
Sub lineemup()
Dim mc As Long
Dim mr As Long
Dim i As Long
Dim lc As Long

mc = 3 'col c
mr = 1
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
lc = Cells(i, Columns.Count). _
End(xlToLeft).Column - mc
Cells(i, mc).Resize(, lc).Copy
Cells(mr, mc - 1).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
mr = mr + lc
Next i
Application.CutCopyMode = False
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question