Transpose 500x300 array efficiently

[Pigankle]

I need to transpose my data so that I can use autofilter on it. This works, but seems like it is ineffecient. Does anyone have a better way of doing this?

Sub Transpose500x300Array()
'

'
ActiveSheet.AutoFilterMode = False

'Copy the original array by selecting a 500x500 box around it.
Range("A1:RZ494").Copy

'Select an out-of-the-way destination
Range("A1001:RZ1494").Select
 
'Paste transposed into the selected area 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'Copy this newly transposed array
Range("A1001:RZ1494").Copy

'Select the original range
Range("A1:RZ494").Select

'Paste back without transposing
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False



Application.CutCopyMode = False

End Sub

I tried to do it without the intermediate step:

Sub Transpose500x5f00Array()

ActiveSheet.AutoFilterMode = False
 
 Range("A1:RZ494").Copy
Range("A1:RZ494").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Application.CutCopyMode = False


End Sub

but it fails with an error message that doesn't fit in it's msgbox, so don't know how to address it: ("If you are using the create from Selection command the row or column containing the proposed frames won't be included in the")

Any improvements out there?

 

[mikerickson]

Try

Dim xArray as Variant

With Range("A1:RZ494")
    xArray = .Value
    .Resize(.Columns.Count, .Rows.Count).Value = Application.Transpose(xArray)
End With

 

[Pigankle]

Splendid - that is much more compact - thank you!

 

[Pigankle]

Weird - that worked several times as a standalone function, but I tried to embed it in another function it didn't, and now the standalone doesn't work anymore either. My exact code is:

Sub Transpose500x500pArray()
'
' SelectRangeOnNewSheet Macro
'

 'Call DeleteColumnsFromWithOn(Deleteable, "<1954", "year")
 Application.Goto Reference:=Worksheets("Deleteable").Range("A122"), Scroll:=True

Dim xArray As Variant

With Range("A1:RZ494")
    xArray = .Value
    .Resize(.Columns.Count, .Rows.Count).Value = Application.Transpose(xArray)
End With

End Sub

"Deleteable" is the name of my sandbox worksheet - it is valid and unprotected.


It fails on the line beginning with ".Resize"
With the message: "Runtime Error '5': Invalid procedure call or argument."

Puzzling.....

 

[mikerickson]

Try this version, it doesn't select.

Sub Transpose500x500pArray()
    Dim xArray As Variant

    With Worksheets("Deleteable").Range("A1:RZ494")
        xArray = .Value
        .Resize(.Columns.Count, .Rows.Count).Value = Application.Transpose(xArray)
    End With
End Sub

 

[Pigankle]

Same error. I tried it on other sheets, though, and the original code does work for them - I must have changed some setting on the sheet "Deleteable," but I can't for the life of me figure out which one it is.

 

[mikerickson]

Are any of the cells merged?

 

[Pigankle]

I'll check when I am back at work on Monday.....Thanks for troubleshooting with me!