macro to transpose Data

[howard]

I have data that starts in F1 and the range can at times stretch to O15-see some sample data below

Book1
	F	G	H
1	933305	933316	933317
2	985140	985147
3	955815	955478	955147
Sheet1

I need a macro to transpose an copy the data in Col Q from row 1 onwards-I should look as follows once the macro has run

Book1
	Q
1	933305
2	933316
3	933317
4	985140
5	985147
6	955815
7	955478
8	955147
Sheet1

Your assistance in resolving this is much appreciated

 

[Fluff]

Re: mqcro to transpose Data

How about

Sub TransposeData()
   Dim ary As Variant
   Dim Nary() As Variant
   Dim r As Long, c As Long, i As Long
   ary = Intersect(ActiveSheet.UsedRange, Range("F:O"))
   For r = 1 To UBound(ary)
      For c = 1 To UBound(ary, 2)
         If Not IsEmpty(ary(r, c)) Then
            ReDim Preserve Nary(0 To i)
            Nary(UBound(Nary)) = ary(r, c)
            i = i + 1
         End If
      Next c
   Next r
   Range("Q1").Resize(i).Value = Application.Transpose(Nary)
End Sub

 

[Peter_SSs]

.. data that starts in F1 and the range can at times stretch to O15

With such a small maximum range I think you could work directly in the worksheet with no detrimental speed effect.

Sub SingleColumn()
  Dim r As Long
  Dim c As Range
  
  For Each c In Range("F1:O15")
    If Len(c.Value) > 0 Then
      r = r + 1
      Cells(r, 17).Value = c.Value
    End If
  Next c
End Sub

 

[howard]

Thanks for the help guys, much appreciated

 

[Fluff]

You're welcome

 

[Peter_SSs]

You're welcome

Ditto