Hi,
I have a routine that pastes an array to a worksheet.
I want to have it call another routine that autosizes all the columns at
once, then if any column widths excede my max, it sets width to max.

Everthing comes to an "Error 13" Wrong Type crash at this line:
Set cols = pasteRange .Columns

I'm not sure if I'm defining the variable wrong or what.

Any help would be great,
Thanks,
MikeZz

Private Sub z20_Set_This_Range_from_Array(rng1Cell As Range, arr() As
Variant, Optional autoFitCols as Boolean)

Dim r, c
Dim cols As Columns
Dim pasteRange As Range
r = UBound(arr, 1) - LBound(arr, 1)
c = UBound(arr, 2) - LBound(arr, 2)

Set pasteRange = Range(rng1Cell, rng1Cell.Offset(r, c))
pasteRange = arr

If IsMissing(autoFitCols) Then
Else
Set cols = pasteRange .Columns
If autoFitCols = True Then _
Call z30_Set_Auto_Column_Size_With_Max(cols, 25)
Set cols = Nothing
End If

End Sub

Sub z30_Set_Auto_Column_Size_With_Max(cols As Columns, cMax)
Dim col As Column

cols.AutoFit

For Each col In cols
If col.Width cMax Then col.Width = cMax
Next col

End Sub

Hi Jessen,
Looks a lot cleaner than my method....Thanks!

The only additional fix I had to make was that Err 424 until I changed
"Col.Width" to "Col.ColumnWidth".

I didn't know that there were two widths in the col range property.
Any idea?

Thanks again for the quick help!
MikeZz

"Per Jessen" wrote:

Hi Mike

Columns (and rows) has to be defined as Range variables. You can call
another Sub to autosize all columns, but I don't see any reason to do it
like that. In one macro it can be done like this:

Private Sub z20_Set_This_Range_from_Array(rng1Cell As Range, arr() As
Variant, Optional autoFitCols As Boolean)
Dim r As Long, c As Long
Dim pasteRange As Range
Dim Col As Range
Dim cMax As Double

r = UBound(arr, 1) - LBound(arr, 1)
c = UBound(arr, 2) - LBound(arr, 2)
cMax = 18

Set pasteRange = Range(rng1Cell, rng1Cell.Offset(r, c))
pasteRange = arr

pasteRange.Columns.AutoFit
For Each Col In pasteRange.Columns
If Col.Width cMax Then Col.Width = cMax
Next
End Sub

Hopes this helps

---
Per

"MikeZz" skrev i meddelelsen
...
Hi,
I have a routine that pastes an array to a worksheet.
I want to have it call another routine that autosizes all the columns at
once, then if any column widths excede my max, it sets width to max.

Everthing comes to an "Error 13" Wrong Type crash at this line:
Set cols = pasteRange .Columns

I'm not sure if I'm defining the variable wrong or what.

Any help would be great,
Thanks,
MikeZz

Private Sub z20_Set_This_Range_from_Array(rng1Cell As Range, arr() As
Variant, Optional autoFitCols as Boolean)

Dim r, c
Dim cols As Columns
Dim pasteRange As Range
r = UBound(arr, 1) - LBound(arr, 1)
c = UBound(arr, 2) - LBound(arr, 2)

Set pasteRange = Range(rng1Cell, rng1Cell.Offset(r, c))
pasteRange = arr

If IsMissing(autoFitCols) Then
Else
Set cols = pasteRange .Columns
If autoFitCols = True Then _
Call z30_Set_Auto_Column_Size_With_Max(cols, 25)
Set cols = Nothing
End If

End Sub

Sub z30_Set_Auto_Column_Size_With_Max(cols As Columns, cMax)
Dim col As Column

cols.AutoFit

For Each col In cols
If col.Width cMax Then col.Width = cMax
Next col

End Sub

Hi Mike

Thanks for your reply. I'm glad you made it work though I used the wrong
property. I knew it looked wrong, but didn't test it :-(

The Width property sets an objects (Application, Range, Window) width in
points, but isn't related to the column width.

Regards,
Per

"MikeZz" skrev i meddelelsen
...
Hi Jessen,
Looks a lot cleaner than my method....Thanks!

The only additional fix I had to make was that Err 424 until I changed
"Col.Width" to "Col.ColumnWidth".

I didn't know that there were two widths in the col range property.
Any idea?

Thanks again for the quick help!
MikeZz

"Per Jessen" wrote:

Hi Mike

Columns (and rows) has to be defined as Range variables. You can call
another Sub to autosize all columns, but I don't see any reason to do it
like that. In one macro it can be done like this:

Private Sub z20_Set_This_Range_from_Array(rng1Cell As Range, arr() As
Variant, Optional autoFitCols As Boolean)
Dim r As Long, c As Long
Dim pasteRange As Range
Dim Col As Range
Dim cMax As Double

r = UBound(arr, 1) - LBound(arr, 1)
c = UBound(arr, 2) - LBound(arr, 2)
cMax = 18

Set pasteRange = Range(rng1Cell, rng1Cell.Offset(r, c))
pasteRange = arr

pasteRange.Columns.AutoFit
For Each Col In pasteRange.Columns
If Col.Width cMax Then Col.Width = cMax
Next
End Sub

Hopes this helps

---
Per

"MikeZz" skrev i meddelelsen
...
Hi,
I have a routine that pastes an array to a worksheet.
I want to have it call another routine that autosizes all the columns
at
once, then if any column widths excede my max, it sets width to max.

Everthing comes to an "Error 13" Wrong Type crash at this line:
Set cols = pasteRange .Columns

I'm not sure if I'm defining the variable wrong or what.

Any help would be great,
Thanks,
MikeZz

Private Sub z20_Set_This_Range_from_Array(rng1Cell As Range, arr() As
Variant, Optional autoFitCols as Boolean)

Dim r, c
Dim cols As Columns
Dim pasteRange As Range
r = UBound(arr, 1) - LBound(arr, 1)
c = UBound(arr, 2) - LBound(arr, 2)

Set pasteRange = Range(rng1Cell, rng1Cell.Offset(r, c))
pasteRange = arr

If IsMissing(autoFitCols) Then
Else
Set cols = pasteRange .Columns
If autoFitCols = True Then _
Call z30_Set_Auto_Column_Size_With_Max(cols, 25)
Set cols = Nothing
End If

End Sub

Sub z30_Set_Auto_Column_Size_With_Max(cols As Columns, cMax)
Dim col As Column

cols.AutoFit

For Each col In cols
If col.Width cMax Then col.Width = cMax
Next col

End Sub