how can I freeze column width (cell size)???

T

Tracey

I have a spreadsheet with formulas.
I show the formulas and resize (autofit) the columns.
I want to freeze the column widths on all the columns then...
.... uncheck show formulas.

Problem: when I uncheck show formulas the columns resize (smaller)
automatically.
Many of the columns re-size to small for the data (pound signs display).

Can someone tell me what I need to do to keep the columns from resizing
after unchecking show formulas?

Thanks, Tracey
 
D

Dave Peterson

How about a little macro?

Option Explicit
Sub testme01()
Dim myColWidths(1 To 256) As Double
Dim iCtr As Long

With ActiveSheet

ActiveWindow.DisplayFormulas = True
.Columns.AutoFit

For iCtr = 1 To .Columns.Count
myColWidths(iCtr) = .Columns(iCtr).ColumnWidth
Next iCtr

ActiveWindow.DisplayFormulas = False

For iCtr = 1 To .Columns.Count
.Columns(iCtr).ColumnWidth = myColWidths(iCtr)
Next iCtr

End With

End Sub

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Dave Peterson

A non-macro method--if you've got xl2k or higher.

Insert a "helper" worksheet.

Do all your showing of formulas and resizing of columns.
ctrl-A (to select all the cells)
edit|copy

to the helper worksheet
edit|paste special|Column widths

Back to the real worksheet.
turn off the formula view

back to the helper worksheet
ctrl-a
edit|copy
back to the real worksheet and
edit|paste special|column widths
 
Top