Transpose Macro - Struggling with Arrays

[Tash Point O]

Hello -

I made a jenky macro that transposes a large set of data. It runs slow because it's a lot of data that it goes through. So I had the bright idea that I wanted to spend 2 days in a row improving this dumb macro I made. I really tried making this an array or a range type macro and keep failing. Can a transpose macro even be array'ed/range'd?

I want to take the values starting in D1 and say through P1 (because it will vary) and transpose them in the C column under that first value you see (for instance take D1:E1 and move it under c1), then hit the next value in D and do the same, till there no longer is a value in D.

I hate to keep asking for help here and I really tried, but I'm new to declaring dimensions and arrays and I'm ready for a beer at this point. Thank you for any input and insights. I am buying VBA for Dummies after work today.

[/IMG]

 

[shg]

I can't relate your description to your example. How about a before and after?

 

[Tash Point O]

I can't relate your description to your example. How about a before and after?

Yes of course here is the after:

 

[shg]

Maybe ...

Sub TPO()
  Dim rArea         As Range

  On Error GoTo NeverMind
  
  For Each rArea In Range("E:E").SpecialCells(xlCellTypeConstants).Areas
    With Range(rArea(1), Cells(rArea.Row, Columns.Count).End(xlToLeft))
      .Copy
      .Offset(1, -1)(1).PasteSpecial Transpose:=True
      .ClearContents
    End With
  Next rArea
  
NeverMind:
End Sub

 

[Tash Point O]

SHG,

That worked PERFECTLY. Thank you so much!!! I always credit in my macros who helped me, so your name is going in it, if you don't mind!

 

[shg]

No need to do so at all. You're welcome.

 

[Rick Rothstein]

Maybe ...

Sub TPO()
  Dim rArea         As Range

  On Error GoTo NeverMind
  
  For Each rArea In [B][COLOR="#FF0000"]Range("E:E")[/COLOR][/B].SpecialCells(xlCellTypeConstants).Areas
    With Range(rArea(1), Cells(rArea.Row, Columns.Count).End(xlToLeft))
      .Copy
      .Offset(1, -1)(1).PasteSpecial Transpose:=True
      .ClearContents
    End With
  Next rArea
  
NeverMind:
End Sub

Why Column E instead of Column D?