XL: Pivot without PasteSpecial? 1

[lameid]

I found the CopyValues procedure below somewhere on the internet (with less / no comments). I added the others easily to handle in place copy and copying formulas either relative or not.

However there is a scenario I'm trying to crack but I'm not seeing an elegant solution, that is replacing Pastespecial with transpose = true.

The only thought I had was to assign the range to an array, pivot that and then write it back to the range... I did not find a way to pivot an array quickly.

Hopefully someone has an elegant solution or I may just have to tolerate the copy/pastespecial.

Public Sub CopyValues(rngSource As Range, rngTarget As Range)
    'Allows for copying one range to target range without actually using copy and paste
    'Not only is it faster to not roundtrip the clipboard, other use of the clipboard will not corrupt execution...
    'One Apparent weakness is does not work when Source and Target are the same
    
    Set rngTarget = rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
    rngTarget.Value = rngSource.Value
 
End Sub
Public Sub ReplaceValues(rngSource As Range)
    'Inplace Paste Special Values: Allows for copying source range values and removing formulas
    'Not only is it faster to not roundtrip the clipboard, other use of the clipboard will not corrupt execution...
            
    rngSource.Value = rngSource.Value
 
End Sub

Public Sub CopyFormulasAbsolute(rngSource As Range, rngTarget As Range)
    'Allows for copying one range to targe range without actually using copy and paste
    'Not only is it faster to not roundtrip the clipboard, other use of the clipboard will not corrupt execution...
    'Copies literal formulas, not relative
    
    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Formula = rngSource.Formula
 
End Sub

Public Sub CopyFormulasRelative(rngSource As Range, rngTarget As Range)
    'Allows for copying one range to targe range without actually using copy and paste
    'Not only is it faster to not roundtrip the clipboard, other use of the clipboard will not corrupt execution...
    'Copies relative reference formulas, not absolute, or A1 move over 2 columns become C1...
    
    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).FormulaR1C1 = rngSource.FormulaZR1C1
 
End Sub

 

[SkipVought]

Hi,

I'm not understanding why you have heartburn with copy PasteSpecial/transpose???

 

[lameid]

I am trying to prevent using the clipboard.

Typically direct object manipulations as in copyvalues are faster. Also, it removes the possibility that someone working while the macro runs puts something on the clipboard other than the intended copy or almost as annoying ends up with an excel range on the clipboard when he pastes. I've experienced the former. It would be less of a concern if the overall process was fast but I'm measuring in minutes not seconds... I might get it there but it is one of those evolved overtime things with multiple authors often doing something expedient rather than good or maintainable. Then again, it does just process a bunch of data so it is just going to take some time no matter what.

I think I have more clarity today... Seems like fastest is range to array, nested for loops to new array, new array to resized target range. That's just my best guess but Excel is not my forte so maybe there is a better way I am missing.

 

[PHV]

What about the WorksheetFunction.Transpose function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

PHV, that looked promising but playing with it in a spreadsheet it seems to only work with 1 dimensional arrays. Since a range can be two dimensional, I think I'm back to the nested for loops, unless I am missing something?

 

[PHV]

only work with 1 dimensional arrays
False, you may pass a full range as parameter and you get a 2 dimensional array of variants.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

In fact, no array needed.
This worked for (xl2007):
Range("D1:M2")=worksheetfunction.Transpose(Range("A1:B10"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Many thanks PHV, I was missing the nuances there.

Public Sub TransposeValues(rngSource As Range, rngTarget As Range)
    'Transpose values from rngSource to rngTarget.  Equivalent to  copy from rngsource to pastespecial transpose to rngTarget
    
    Set rngTarget = rngTarget.Resize(rngSource.Columns.Count, rngSource.Rows.Count) 'Note resizing columns to rows and rows to columns because transposing
    rngTarget = WorksheetFunction.Transpose(rngSource)
 
End Sub