Multiple Colums & Rows Copy & Paste - Transpose - Looped

[hcabs99]

Hi All

I have a table with multiple Columns & rows (number of columns & rows will vary between Sheets) , i need excel to take each row individually , copy all the populated columns , paste (transpose) into next free cell on a new sheet, then move to the next (populated ) row and repeat. Number of rows will change.

I have a little experience of looping functionality , but i would appreciate some help here.

Let me know if i need to provide any more information

Cheers

Phil

 

[Fluff]

Try

Sub hcabs99()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   
   With Sheets("Source")
   Ary = .Range("B38", .Range("B" & Rows.Count).End(xlUp).Offset(, 34)).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 2)
   For r = 1 To UBound(Ary)
      For c = 9 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            rr = rr + 1
            Nary(rr, 1) = Ary(r, 1)
            Nary(rr, 2) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Conversion").Range("A1").Resize(rr, 2).Value2 = Nary
End Sub

 

[hcabs99]

Try

Sub hcabs99()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   
   With Sheets("Source")
   Ary = .Range("B38", .Range("B" & Rows.Count).End(xlUp).Offset(, 34)).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 2)
   For r = 1 To UBound(Ary)
      For c = 9 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            rr = rr + 1
            Nary(rr, 1) = Ary(r, 1)
            Nary(rr, 2) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Conversion").Range("A1").Resize(rr, 2).Value2 = Nary
End Sub

Perfect.. Excellent, Many thanks for your help!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[hcabs99]

You're welcome & thanks for the feedback

Hi, Sorry, picked up a new requirement now i started to test this.. It now transpires that on some occasions , i have 2 Row identifiers in the source data in 2 columns . So B38 & B39 in the source file, so i need to create a new column in Column B for the conversion for the 2nd identifier

So now it looks like

Source

Row Identifier

Identifier1 , Identifier2 - Result1, Result2, Result3
IdentiferAA, IdentiferBB - Result 4, Result5

Result

Identifer1, Idenfier2, Result1
Identifer1, Idenfier2, Result2
Identifer1, Idenfier2, Result3
IdentiferAA, IdentiferBB - Result 4
IdentiferAA, IdentiferBB - Result5

Can this be tweaked? Note, the 2nd column may not always be populated. Although if its too difficult ,i could insist on them putting a * in or something

thanks, and sorry for the changes

 

[Fluff]

You have stated that your identifiers are in B38 & B39, but have shown them as B38 & C38.
Which is it?