Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru & format all columns
I need to cycle thru each column, autofit it, and add 2 character
spaces. My current code is: Cells.Select Selection.EntireColumn.Hidden = False Selection.EntireRow.Hidden = False Cells.EntireColumn.AutoFit Range("A1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("B1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("C1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("D1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Etc, etc, etc, all the way to Range("IV1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Although this is functional, it looks like a mess. Any help in cleaning it up would be greatly appreciated. ~Arawn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru & format all columns
From vbe HELP. Change *2 to +2.
ColumnWidth Property See Also Applies To Example Specifics Returns or sets the width of all columns in the specified range. Read/write Variant. Remarks One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used. Use the Width property to return the width of a column in points. If all columns in the range have the same width, the ColumnWidth property returns the width. If columns in the range have different widths, this property returns Null. Example This example doubles the width of column A on Sheet1. With Worksheets("Sheet1").Columns("A") .ColumnWidth = .ColumnWidth * 2 End With -- Don Guillett SalesAid Software "Arawn" wrote in message om... I need to cycle thru each column, autofit it, and add 2 character spaces. My current code is: Cells.Select Selection.EntireColumn.Hidden = False Selection.EntireRow.Hidden = False Cells.EntireColumn.AutoFit Range("A1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("B1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("C1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("D1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Etc, etc, etc, all the way to Range("IV1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Although this is functional, it looks like a mess. Any help in cleaning it up would be greatly appreciated. ~Arawn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru & format all columns
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru & format all columns
This might work:
Sub colwidth() Dim clastcol As Integer lastcol = ActiveSheet.Cells(1, 255).End(xlToLeft).Column Cells.Select With Selection .EntireColumn.Hidden = False .EntireRow.Hidden = False .EntireColumn.AutoFit End With For i = 1 To lastcol Cells(1, i).ColumnWidth = Cells(1, i).ColumnWidth + 2 Next i End Sub Arawn wrote: I need to cycle thru each column, autofit it, and add 2 character spaces. My current code is: Cells.Select Selection.EntireColumn.Hidden = False Selection.EntireRow.Hidden = False Cells.EntireColumn.AutoFit Range("A1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("B1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("C1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Range("D1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Etc, etc, etc, all the way to Range("IV1").Select Wide = Selection.ColumnWidth Selection.ColumnWidth = Wide + 2 Although this is functional, it looks like a mess. Any help in cleaning it up would be greatly appreciated. ~Arawn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle thru & format all columns
Thanks for the help guys, both solutions worked (One for each
application that I have). The help is greatly appreciated! ~Arawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a graph with 2-cycle X 3-cycle log-log graph paper? | Charts and Charting in Excel | |||
cycle time | Charts and Charting in Excel | |||
Time Cycle | Excel Discussion (Misc queries) | |||
How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) | |||
How do I cycle through all of the worksheets | Excel Discussion (Misc queries) |