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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.