Paste: trouble with transposing

[beauriddly]

Hi,

I need to go from format "a)" to "b)".
This is more complicated than a regular transpose, as you will see:

a)

a1	b1
a2	b2
a3	b3
a4	b4

<tbody>
</tbody>

b)

a1

b1

a2

b2

a3

b3

a4

b4

<tbody>
</tbody>

Thank you in advance!!

T

 

[Rick Rothstein]

I'd still recommend the native formula route, faster than VBA and easier to implement.

I got the impression that when the OP said this...

I need to go from format "a)" to "b)".
This is more complicated than a regular transpose

(especially the "transpose" part) that he wanted to rearrange his existing data. If I am right, that would mean to implement a formula solution would entail copying the formula cells and then using PasteSpecial/Values to remove the formulas so that the original data from Rows 2 on down could then be cleared. If my guess is correct, I personally think a macro solution would be easier overall.

 

[Tom Urtis]

Different impressions...mine was what he said: display a 4x2 range into a 1x8 range. Destroying the original data is not a frequent request and wasn't mentioned here. In any case, native formulas return values faster than VBA and allows for a change in the original data range to be immediately updated in those 8 formulas without an event procedure or rerunning a macro.

 

[Rick Rothstein]

Different impressions...mine was what he said: display a 4x2 range into a 1x8 range. Destroying the original data is not a frequent request and wasn't mentioned here. In any case, native formulas return values faster than VBA and allows for a change in the original data range to be immediately updated in those 8 formulas without an event procedure or rerunning a macro.

Oh, I do not disagree with you regarding the speed of formulas... as long as the OP wants to keep his original data along with the transformed data... I was just noting if my guess was right (that the OP did not want to retain the original data in place), the Copy/PasteSpecial/Delete operation that would be needed to use the formula method might make that solution more cumbersome in the end. Anyway, our guesses aside, why did you use a Volatile formula for the originally requested horizontal layout... wouldn't this non-Volatile modification to your formula do the same thing...

=INDEX($A:$B,INT((COLUMN()+1)/2),MOD(COLUMN()+1,2)+1)

 

[Tom Urtis]

That formula would do the same thing. With the horizontal formula, I did not focus on the volatile vs non volatile aspect or the one-less function call which yours has -- which I should have if I thought it through with the same zeal as my vertical formula that used INDEX -- even though there is disagreement between Microsoft (who claims INDEX is volatile) and users who claim it is not since version 97, and probably really isn't. Unrelated comment, yet another reason to not treat Microsoft's documentation as the final word.

Moving on to climb other mountains.

 

[mirabeau]

Team,

mirabeau's code ran in 0.016 seconds

hiker95's code ran in 0.00 seconds

Hiker95,

The codes do different things, so unsurprising the times differ.


Post#4 was posted "just for interest" as a concise code with minimal declarations that transferred range format properties (bold, internal color etc) as well as range values. OP didn't specify whether or not wanted just values. After all, ranges are ranges and have properties other than just values.


Transferring just values, pace your code, is an easier task and consequently is likely to be faster.


If you like reasonably concise fast code to transfer only values, then consider something like this

Sub transp() 
Dim a, b&, c()

With Cells(1).CurrentRegion
    If .Count < 2 Then Exit Sub
    ReDim c(1 To .Count)
    For Each a In Application.Transpose(.Cells)
        b = b + 1: c(b) = a
    Next
    .ClearContents
    .Resize(1, b) = c
End With

End Sub

If you'd prefer, you could make your own code a bit faster by eliminating the Application.Transpose, by just removing the bits in red. You might also put in an error control to prevent it failing its own simplest case, i.e. just one cell.

Option ExplicitOption Base 1
Sub ReorgData()
' hiker95, 01/13/2013
' http://www.mrexcel.com/forum/excel-questions/679053-paste-trouble-transposing.html
Dim i As Variant, o As Variant
Dim r As Long, c As Long, rr As Long
i = Range("A1").CurrentRegion
ReDim o(1 To UBound(i, 1) * UBound(i, 2), 1 To 1)
rr = 0
For r = 1 To UBound(i, 1)
  For c = 1 To UBound(i, 2)
    If i(r, c) <> "" Then
      rr = rr + 1
      o(rr, 1) = i(r, c)
    End If
  Next c
Next r
Range("A1").CurrentRegion.ClearContents
Range("A1").Resize(, UBound(o)).Value = Application.Transpose(o)
Erase i
Erase o
End Sub

 

[Rick Rothstein]

If you like reasonably concise fast code to transfer only values, then consider something like this

Sub transp() 
Dim a, b&, c()

With Cells(1).CurrentRegion
    If .Count < 2 Then Exit Sub
    ReDim c(1 To .Count)
    For Each a In Application.Transpose(.Cells)
        b = b + 1: c(b) = a
    Next
    .ClearContents
    .Resize(1, b) = c
End With

End Sub

Just wondering if you saw the code I posted in Message #8 (single loop like yours but avoids using Transpose)?

 

[mirabeau]

Just wondering if you saw the code I posted in Message #8 (single loop like yours but avoids using Transpose)?

Yes Rick. Of course I saw it.

I even tried it out. Works OK for 2 columns and up to 8192 rows without changing internal parameters. But gives wrong result without telling the user if 3 or more columns. Hence not practically useful as it stands.

It also can't handle its own most elementary case (of only one cell) without error control, which you don't include. Also it doesn't transpose ranges, which the Excel Transpose function does do. Yours only transposes values.

Quite fast, although I think if pushed my Post# 15 code is slightly faster. Latter also works in cases where yours, as it stands, doesn't.

You seem to see merit in avoiding Application.Transpose. I suggested Hiker95 omit that from his code but included it in mine, even though it makes no difference to the output whether I use

For Each a In Application.Transpose(.Cells)

or

For Each a In .Cells


So why did I include Application.Transpose? There's a reason. I leave it to you to figure that one out?

 

[hiker95]

Team,

I guess it comes down to interpretation and style.

That is why I like to ask for a screenshot of before and after.

And, I do enjoy the macros and formulae that are presented - what a great place for learning.