How to reset column to standard width?

  • Thread starter Thread starter Rich Pasco
  • Start date Start date
R

Rich Pasco

How can I remove the width setting from a column, so that its width
will track future changes in the "standard width" for that spreadsheet?

I'm using Excel 2000 9.0.6926 SP-3 under Windows 2000.

- Rich
 
As Bubba said, It depends on what your definition of "standard width" is
activecolumn.columnwidth=8.43
 
Don said:
As Bubba said, It depends on what your definition of "standard width" is
Click to expand...

No, I am not talking about *setting* the width of a column equal to that
which happens to be the current "standard width." I am talking about
removing the setting entirely, so that the column will take on whatever
values the "standard width" is set to in the future.

If this difference is not clear to you, please don't cloud the issue.

- Rich
 
Apparently what I want wasn't clear to some, so let me try again.
If I invoke Format / Column / Standard Width, and enter a new value,
then most (but not all) of my columns change width to the new value.
Some (those columns for whom I had previously explicitly set a width)
stay stuck at the previously set width. I want to remove that setting
so the next time I invoke Format / Column / Standard Width, *all*
columns will change width. How?

- Rich
 
Apparently what I want wasn't clear to some, so let me try again.
If I invoke Format / Column / Standard Width, and enter a new value,
then most (but not all) of my columns change width to the new value.
Some (those columns for whom I had previously explicitly set a width)
stay stuck at the previously set width. I want to remove that setting
so the next time I invoke Format / Column / Standard Width, *all*
columns will change width. How?

- Rich
Click to expand...

Select the sheet first, then do the format. That'll change all columns to
your new 'standard' width.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!
 
If this difference is not clear to you, please don't cloud the issue.


Define the default column width
1.. Right-click a sheet tab, and then click Select All Sheets on the
shortcut menu
2.. On the Format menu, point to Column, and then click Standard Width.
3.. Type a new measurement.
The number that appears in the Standard column width box is the average
number of digits 0-9 of the standard font that fit in a cell.

To define the default column width for all new workbooks and worksheets,
create a workbook template and a worksheet template.

Sub SetColWidths()
x = ActiveSheet.StandardWidth
Sheets.Select
Sheet1.Activate
ActiveSheet.Cells.Select
Selection.ColumnWidth = x
Sheet1.Select
End Sub
 
...
...
Define the default column width
1.. Right-click a sheet tab, and then click Select All Sheets on the
shortcut menu
2.. On the Format menu, point to Column, and then click Standard Width.
3.. Type a new measurement.
The number that appears in the Standard column width box is the average
number of digits 0-9 of the standard font that fit in a cell.

To define the default column width for all new workbooks and worksheets,
create a workbook template and a worksheet template.

Sub SetColWidths()
x = ActiveSheet.StandardWidth
Sheets.Select
Sheet1.Activate
ActiveSheet.Cells.Select
Selection.ColumnWidth = x
Sheet1.Select
End Sub
Click to expand...
...

When you don't understand a problem, you really don't have the vaguest memory of
the faintest impression of the shadow of a clue.

The OP wants to do something that in 123 or Quattro Pro or similar spreadsheets
would be called resetting the column width. Unfortunately, this is a feature
Excel has always lacked. Once set, either on purpose or by Excel's ever so
helpful autofitting, it can't be reset so that future changes to the worksheet's
standard width automatically resize these columns.

The only way to do this in Excel is to insert a new column, move (i.e., *cut*
and paste) the entire unintentionally resized column into the inserted column,
and delete the unintentionally resized column. Unfortunately, any future entries
into the cells that caused autofitting resizing may cause the same problem. One
very kludgy way to avoid this is to precede all entries with a character like |
(vertical bar) - even (especially?!) formulas - then use Edit > Replace to
remove the leading character.
 
...
...
The only way to do this in Excel is to insert a new column, . . .
Click to expand...

Needs elaboration. You must insert columns immediately to the right of a column
that has standard width. Inserted columns assume the width of the existing
column immediately to their left.
 
Don said:
Define the default column width
1.. Right-click a sheet tab, and then click Select All Sheets on the
shortcut menu
2.. On the Format menu, point to Column, and then click Standard Width.
3.. Type a new measurement.
The number that appears in the Standard column width box is the average
number of digits 0-9 of the standard font that fit in a cell.
Click to expand...

Yes, I know how to define the default (or "standard") column width.
You're telling me something I already know, and not answering my question.

What I don't know is how to remove the "custom" width that I have
applied to *some* columns, which prevents them from inheriting the
default ("standard") with set by your procedure above.

- Rich
 
Harlan said:
When you don't understand a problem, you really don't have the vaguest memory of
the faintest impression of the shadow of a clue.

The OP wants to do something that in 123 or Quattro Pro or similar spreadsheets
would be called resetting the column width.
Click to expand...

Thank you! At least *someone* is actually readint the question that I
am asking!
Unfortunately, this is a feature
Excel has always lacked. Once set, either on purpose or by Excel's ever so
helpful autofitting, it can't be reset so that future changes to the worksheet's
standard width automatically resize these columns.
Yikes!

The only way to do this in Excel is to insert a new column, move (i.e., *cut*
and paste) the entire unintentionally resized column into the inserted column,
and delete the unintentionally resized column. Unfortunately, any future entries
into the cells that caused autofitting resizing may cause the same problem. One
very kludgy way to avoid this is to precede all entries with a character like |
(vertical bar) - even (especially?!) formulas - then use Edit > Replace to
remove the leading character.
Click to expand...

What a kludgy work-around for a tragic omission in the design of the
product!

Thank you

- Rich
 
Harlan said:
Needs elaboration. You must insert columns immediately to the right of a column
that has standard width. Inserted columns assume the width of the existing
column immediately to their left.
Click to expand...

This is even more tragic. Not only does manually setting a column's
width permanently prevent it from ever tracking the default ("standard")
width, it also prevents any columns inserted to its the right from doing
so either.

Thank you again for the thorough answer, clearly understanding my
question. Even if it is bad news, it is appreciated.

- Rich
 
I think tragic is a bit melodramatic, don't you?

In any case, this will reset the columns. Put it in a regular module
in your Personal.xls file and attach it to a toolbar button:

Public Sub ResetColumnWidthToStandard()
Selection.UseStandardWidth = True
End Sub

One click and you're done.
 
Select the sheet first, then do the format. That'll change all columns to
your new 'standard' width.
--
Click to expand...
Ah, I see what you mean. Harlan's got it , I think.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!
 
Back
Top