Originally Posted by
User_Renee
If I have several items to repeat/duplicate in according to the number of descriptions, shall I modify from the Ubound function, such as Ubound(var,2) for two "Items" (ex: Book, Children)?
Not sure I fully understand, if you add more rows and more columns to the data the code should pick that up and transpose the new data.
I have just noticed an error in the sizing of the output array - I will provide fresh code below which I have annotated to help understand (in my words) what is going on.
This line:
ReDim oVar((UBound(var)) * (UBound(var, 2) - 1) - 1, 1)
Should be:
ReDim oVar(UBound(var) * UBound(var, 2) - 1, 1)
Annotated code below:
Sub test()
Dim rng As Range, var As Variant, x As Long, y As Long, z As Long
Set rng = Sheet1.UsedRange ' set rng to be the used range of the sheet
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1) ' reset rng to be the same range but without header
var = rng.Value ' set var to be the values in rng, rows/ columns
' resize the output array to the size of the new data, rows * columns
' UBound(var) = var rows
' UBound(var, 2) = var columns
' the -1 part is becuase the var array starts at 1 and the output array starts at 0
' the 1 at the end is the amount of columns in the output array, as oVar starts at 0 the 1 means two columns
' Rows: (Rows * columns)-1 Columns:1
ReDim oVar(UBound(var) * UBound(var, 2) - 1, 1)
For x = 1 To UBound(var) ' loop through var rows (x)
For y = 2 To UBound(var, 2) ' loop through var columns (y)
' z = 0 the first time we get to this line, z is the output array row number, 0 is the column number
' var(x,1) is the item from the var array
oVar(z, 0) = var(x, 1)
' the next line writes to the second column of the output array (1)
' this is where the (y) comes into play
' it loops through the columns (y) of var (descriptions) and writes them to the rows (z) of oVar
oVar(z, 1) = var(x, y)
z = z + 1 ' increment z by one so the next record is put into the next row of oVar
Next y ' next var column
Next x ' next var row
' next we resize range A2 on sheet2 to be the same size as oVar and then write oVar to that range
Sheet2.Range("A2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub
Hope this helps