Autosize ALL cells in Excel 2003 workbook to fit data

[tkaz]

I have a workbook that serves as a weekly costing report. Data is entered in several fields, other columns calculate based on the input. There are several columns that span two pages.

My issue is that sometimes, I don't notice that the cell width is too small to display the value...I get the ######'s.

I found some code that was supposed to fix this, but it only works on active input cells. Is there anything that will autosize ALL cells, even calculated from the input?

Thanks for any help you can give. This is time consuming each week to make sure all cells are properly sized.

BTW...Autofit DOES NOT work!

 

[SkipVought]

SELECT ALL CELLS.

Double-click ANY COLUMN CELL BOUNDARY between A, B, C etc.

Sam applies for ROWS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, notice how the CURSOR changes in that area of the sheet.
[tt]
<-|->
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tkaz]

This is the code that I'm using. It works FINE on the cells into which I input data, but not on some of my calculated cells.

Private Sub Workbook_SheetChange(ByVal Sh As Object _, ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
cell.EntireColumn.AutoFit
Next cell
End Sub

I really don't want to have to have to AUTOFIT every column, every time just to make sure that the cells display. I assume that is what you are suggesting with the double-click between the columns. I also don't want to highlight the entire sheet and format/column/autofit. I have some hidden columns and some that are merged and I don't want to fit into one column.

I do appreciate the response!

 

[SkipVought]

I really don't want to have to have to AUTOFIT every column, every time just to make sure that the cells display. I assume that is what you are suggesting with the double-click between the columns. I also don't want to highlight the entire sheet and format/column/autofit. I have some hidden columns and some that are merged and I don't want to fit into one column.

Autofit does not work IF you have merged cells. Merge is a CURSE!

forum68 is not a VBA code forum. It is an native application forum.

Please use forum707 for VBA questions.

If you are coding a solution , loop thru the columns in the used range, autofitting each column individually.

Alternatively, make a LIST of the columns that are hidden. AutoFit ALL columns and then HIDE the requisite columns. You could also specify the explicit column widths for merged areas in a list.

All kinds of ways to skin a cat. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tkaz]

Thanks...sorry I used the wrong forum!

Again, I appreciate the help you've given me.

 

[SkipVought]

Well nothing in your question suggested that you were looking for a VBA solution.

You CAN find AutoFit in Excel HELP in addition to VBA HELP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tkaz]

Thank you, Skip.

 

[Gavona]

For completeness the non-VBA way:
1.Select the entire worksheet or used range. I would do this in xl 2003 by clicking on the rectangle at the intersect of the row and column headers.
2.Format, Row, Autofit
3. Format, Column, Autofit

This, and the VBA solutions, will not work with merged cells or cells that have more than 255 characters.

Gavin

 

[tkaz]

Thanks...I think that I have resolved this with my VB code. I appreciate all the help!