Set Column Width
 

I would like to AutoFit all the columns in the UsedRange of a worksheet
unless that would make the column wider than 40. In that case I want to set
the column to 40.

I get an Object Required error 424 message when I attempt the following:

Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In UsedRange
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next
End Sub


I just want to automate a report that my predecessor did manually. Could
anyone supply me with a brief explanation of the mistake I've made? I'd like
to avoid it in the future.
Thanks in advance for any help.
--
tj

Set Column Width
 

Change "UsedRange" to "ActiveSheet.UsedRange".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"tjtjjtjt" wrote in message
...
I would like to AutoFit all the columns in the UsedRange of a
worksheet
unless that would make the column wider than 40. In that case I
want to set
the column to 40.

I get an Object Required error 424 message when I attempt the
following:

Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In UsedRange
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next
End Sub


I just want to automate a report that my predecessor did
manually. Could
anyone supply me with a brief explanation of the mistake I've
made? I'd like
to avoid it in the future.
Thanks in advance for any help.
--
tj

Set Column Width
 

UsedRange is a property of a WorkSheet (or a ListObject). Try

For Each col In ActiveSheet.UsedRange


In article ,
"tjtjjtjt" wrote:

I would like to AutoFit all the columns in the UsedRange of a worksheet
unless that would make the column wider than 40. In that case I want to set
the column to 40.

I get an Object Required error 424 message when I attempt the following:

Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In UsedRange
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next
End Sub


I just want to automate a report that my predecessor did manually. Could
anyone supply me with a brief explanation of the mistake I've made? I'd like
to avoid it in the future.
Thanks in advance for any help.

Set Column Width
 

Thanks, Chip.
--
tj


"Chip Pearson" wrote:

Change "UsedRange" to "ActiveSheet.UsedRange".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"tjtjjtjt" wrote in message
...
I would like to AutoFit all the columns in the UsedRange of a
worksheet
unless that would make the column wider than 40. In that case I
want to set
the column to 40.

I get an Object Required error 424 message when I attempt the
following:

Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In UsedRange
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next
End Sub


I just want to automate a report that my predecessor did
manually. Could
anyone supply me with a brief explanation of the mistake I've
made? I'd like
to avoid it in the future.
Thanks in advance for any help.
--
tj

Set Column Width
 

Thanks, JE.


--
tj


"JE McGimpsey" wrote:

UsedRange is a property of a WorkSheet (or a ListObject). Try

For Each col In ActiveSheet.UsedRange


In article ,
"tjtjjtjt" wrote:

I would like to AutoFit all the columns in the UsedRange of a worksheet
unless that would make the column wider than 40. In that case I want to set
the column to 40.

I get an Object Required error 424 message when I attempt the following:

Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In UsedRange
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next
End Sub


I just want to automate a report that my predecessor did manually. Could
anyone supply me with a brief explanation of the mistake I've made? I'd like
to avoid it in the future.
Thanks in advance for any help.

Set Column Width
 

Another thing you may want to change:

Option Explicit
Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In ActiveSheet.UsedRange.Columns
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next col
End Sub

If you don't include .columns in activesheet.usedrange.columns, then you're
cycling through all those cells in each column.



tjtjjtjt wrote:

I would like to AutoFit all the columns in the UsedRange of a worksheet
unless that would make the column wider than 40. In that case I want to set
the column to 40.

I get an Object Required error 424 message when I attempt the following:

Sub SetColWidth()
Dim col As Range

Cells.EntireColumn.AutoFit

For Each col In UsedRange
If col.ColumnWidth 40 Then
col.ColumnWidth = 40
End If
Next
End Sub

I just want to automate a report that my predecessor did manually. Could
anyone supply me with a brief explanation of the mistake I've made? I'd like
to avoid it in the future.
Thanks in advance for any help.
--
tj

--

Dave Peterson