Tom - your assumption is correct. I did not want other columns subject to
Autofit so as to preserve the layout. I ended up modifying your code to use
"cell.Columns.AutoFit" as there we some columns which had text in cells
above the column that extended over several columns. Thus setting the entire
column width made the column the width of that string of text. So i reverted
to the cell rather than the entire column.

"Tom Ogilvy" wrote in message
...
Columns.Autofit

would be appropriate then.

But while I considered that, I assumed he might have columns he didn't
want
altered.

--
Regards,
Tom Ogilvy

"broro183" wrote
in
message ...

hi,
Tom, I know you have answered the question that was asked & have set
your code solution to do header rows but is it necessary?

Why not just just use the following code?
Range("A:Z").EntireColumn.Autofit

My quick (single) test appeared to show that it doesn't upset cells
that have wrapped text and I'm sure it would be slightly quicker than
running a "for each" (I haven't yet checked out how to time my
macros).
The only downside I can see is that it may make some columns narrower
than they currently are - not a problem in itself, but may result in a
changed page layout for printing.

Just curious,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Tom Ogilvy Wrote:
for each cell in Range("A2:Z2")
if instr(1,cell.Text,"###",vbTextCompare) then
cell.EntireColumn.Autofit
end if
Next

--
Regards,
Tom Ogilvy



"qwerty" wrote in message
...
If a column is not wide enough for the displayed number, it shows #
signs.
In VBA is there a way to detect which cells in which columns have
that ###
so I can use column autofit to set to the width needed?




--
broro183
------------------------------------------------------------------------
broro183's Profile:
http://www.excelforum.com/member.php...o&userid=30068
View this thread:
http://www.excelforum.com/showthread...hreadid=531212