VBA - Copy values in variable and transpose

[cozzagiorgi]

Hi

I want to stock my range (lets say A6:A13) in to a variable. But the size of this range can change according to what the user puts in.
Later on I will take this variable, transpose it and paste it in the range A1:Z40

The code I have is:

Sub AuswahlErstellt()


Dim Proben() As Variant


varProben = Range("A6:A13").Value
Range("A17:Z17") = Application.WorksheetFunction.Transpose(varProben)


End Sub

Of course, the range A17:Z17 is much bigger than what I would need so it gives me #NV errors. How can i do that without those errors? I am sure there is a better solution for what I am doing...

 

[footoo]

Sub AuswahlErstellt()
Dim Proben As Range: Set Proben = Range("A6:A13")
Range("A17").Resize(, Proben.Cells.Count) = Application.WorksheetFunction.Transpose(Proben)
End Sub

 

[cozzagiorgi]

That solves the problem of the #NV , thank you!
But what if I don't know how big the range A6:A13 will be? Can I set a "dynamic" range?

 

[footoo]

But what if I don't know how big the range A6:A13 will be? Can I set a "dynamic" range?

It can be any size (in single column).
This handles it :

Range("A17").Resize(, Proben.Cells.Count) = Application.WorksheetFunction.Transpose(Proben)

 

[cozzagiorgi]

I am not clear, sorry.
The first line is: Dim Proben As Range: Set Proben = Range("A6:A13")
This way the code will only look at A6:A13, but what if that range gehts bigger?

 

[footoo]

I am not clear, sorry.
The first line is: Dim Proben As Range: Set Proben = Range("A6:A13")
This way the code will only look at A6:A13, but what if that range gehts bigger?

You can set the range to whatever you want.
If you mean how to set a dynamic range :
Set rng=Range([A6],Cells(Rows.Count,"A").End(xlUp))

Also, if the range might cover more than one column then :

Sub AuswahlErstellt()
Dim Proben As Range: Set Proben = Range("A6:B13")
Range("A17").Resize(Proben.Columns.Count, Proben.Rows.Count) = Application.WorksheetFunction.Transpose(Proben)
End Sub

 

[cozzagiorgi]

Sorry if I still don't get it.
Of course I could set my range whatever I want. But I will not know how big this range will be. So instead of writing A6:A13 I need Excel to adapt to the number of used cells.

 

[footoo]

See my edited post.

 

[cozzagiorgi]

Thats it, thank you!