| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change Cell Width WITHOUT changing Column width | Excel Discussion (Misc queries) | |||
| how do I create multiple column width in the same column in excel | Excel Discussion (Misc queries) | |||
| How to make cell width different than the column width it lies in | Excel Discussion (Misc queries) | |||
| Change the width of a single column in a column chart | Charts and Charting in Excel | |||
Linear Mode
