Setting column width if #### appears in column

[qwerty]

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?

 

[Tom Ogilvy]

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

 

[Doug Glancy]

Tom,

I didn't realize you could do that - very cool. I was trying to think of a
solution to cover down to if 0 #'s are displayed. This is what I came up
with, but I'm guessing I missed something:

For Each cell In Range("A2:Z2")
If (Len(cell.Text) = 0 And cell.Value <> "") Or InStr(1, cell.Text, "#",
vbTextCompare) Then
cell.EntireColumn.AutoFit
End If
Next

What do you think?

Doug

 

[qwerty]

Danke. Merci. Gracias. Thank you.

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

 

[broro183]

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]

Columns.Autofit

would be appropriate then.

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

 

[broro183]

Thanks Tom

I thought that would be the case but I was just curious about your
reasoning.Also, a good point ("columns" rather than "entirecolumn").

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

 

[Tom Ogilvy]

Doug,
I chose multiple #'s because I figured 1 might be legitimate.

Anyway, I wasn't going for a generalized solution - just giving some hints.

I guess you are attacking a column too narrow to even show the #; that is
good.

Just as an observation, it would also "attack" a cell that was formatted
with ;;;

 

[Tom Ogilvy]

No, just plain columns (not as a substitute for entirecolumn)

Rather than
Range("A:Z").EntireColumn.Autofit

just
Columns.Autofit
or
Activesheet.Columns.Autofit

Blank columns are not affected.

 

[Doug Glancy]

Tom,

I knew you weren't going for a generalized solution - yours was exactly what
the OP asked for. You just got me thinking.

As to the ";;;" formatting, I didn't think of that, but it doesn't seem to
be affected, i.e., the column width isn't adjusted when it has that
formatting (at least in my attempts). Certainly not a crucial issue for me,
but if you have any further explanation, I'm interested.

Doug

 

[Doug Glancy]

Now I think I understand. With ;;;, the If statement would be true, and if
there was a wider entry below that was purposely narrow, my code would widen
the column to that width.

Doug

 

[Tom Ogilvy]

? len(activecell.Text)
0
? activecell.Value<>0
True

It passes your test to take action. I suspect the reason you don't see an
effect is because there are no other displayed values in the column. If
another cell was displayed (such as a text entry bleeding over into another
column), it would change the width. Granted, that cell itself does not
control the width, but would suffice to trigger the change if other
conditions prevailed. .

 

[Tom Ogilvy]

OK. See you later post below.

 

[qwerty]

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.

 

[Doug Glancy]

Thanks Tom.

Doug

OK. See you later post below.