PDA

View Full Version : [SOLVED] Excel VBA to Transpose lists



simora
08-31-2017, 12:53 PM
I have a set of categories in column A, and each one has a list attached to it.
What is the best way using VBA to transpose each of those categories so that each one has its own column with the associated code number, leaving two (2) BLANK spaces between each category.

See attached worksheet Sheet1

Using Office 2007

Kenneth Hobs
08-31-2017, 03:13 PM
Sub TranposeAB()
Dim r As Range, c As Range, sc As Range

Set r = Intersect(Range("A2", Cells(Rows.Count, "B").End(xlUp)), _
ActiveSheet.UsedRange)
Set sc = Range("G1")

For Each c In r.Rows
If c.Cells(1).Value = "" And c.Cells(2).Value = "" Then
If c.Cells(2).Offset(1).Value <> "" Then Set sc = Cells(1, sc.Column + 4)
GoTo NextC
End If
c.Copy sc.Resize(, 2)
Set sc = sc.Offset(1)
NextC:
Next c
End Sub

simora
08-31-2017, 09:27 PM
Thanks Kenneth:
Your code works perfectly. Can you please clarify this bit for me.
NextC:
Next c
Trying to determine if c is = C and its use in the code logic.

mdmackillop
09-02-2017, 02:55 AM
NextC: is a label used by GoTo NextC to skip the intervening lines of code

simora
09-02-2017, 11:08 AM
Thanks mdmackillo (http://www.vbaexpress.com/forum/member.php?87-mdmackillop) :

I was wondering about that. Using NextC: is a label really threw me for a curve.