CODEWITHSUNDEEP
arraysexcelpastevba
Dullspark
Dullspark

Reputation: 215

Excel VBA - Transpose an Array Horizontally

I have an Array (dict1) that i want to paste from cells T1-Z1 using the following code

With Sheets("Raw_Data")
  .Range(.Cells(1, 20), .Cells(1, 26)).Resize(dict1.Count).Value = Application.Transpose(dict1.keys)
End With

When i use this however, it pastes the values in the Array from T1-T7, and then copies this from columns T-Z. Is there anyway I can change this so it transposes the array to go horizontally

Upvotes: 1

Views: 2837

Answers (2)

antonsachs
antonsachs

Reputation: 55

The original array was loaded from a vertical range

avarPercentValues() = .Range(cstrColTotals & clngAverageSpread & ":" & cstrColTotals & clngValidGain).Value2

The easiest and fastest way to save it horizontally:

.Range(cstrColTAvgSpread & lngDataRow & ":" & cstrColTValidGain & lngDataRow).Value2 = Application.Transpose(avarPercentValues())

cxxxYY are constants for columns On my machine I lose 4 µs om transpose.

Upvotes: 0

Mrig
Mrig

Reputation: 11702

Instead of 

.Range(.Cells(1, 20), .Cells(1, 26)).Resize(dict1.Count).Value = Application.Transpose(dict1.keys)

use

.Range("T1").Resize(1, UBound(Application.Transpose(dict1.keys))) = dict1.keys

Try following code

Sub Demo()
    Dim dict1 As Object
    Set dict1 = CreateObject("Scripting.Dictionary")

    'assume following are the disctionary items
    dict1.Add "Aaa", 1
    dict1.Add "Bbb", 2
    dict1.Add "Ccc", 3
    dict1.Add "Ddd", 4
    dict1.Add "Eee", 5
    dict1.Add "Fff", 6
    dict1.Add "Ggg", 7

    'output dictionary horizontally
    With Sheets("Raw_Data")
        .Range("T1").Resize(1, UBound(Application.Transpose(dict1.Keys))) = dict1.Keys
    End With
End Sub

This reults in following image.

enter image description here

Upvotes: 2

Related Questions