Setting column width if #### appears in column
Thanks Tom.
Doug
"Tom Ogilvy" wrote in message
...
OK. See you later post below.
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
? 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. .
--
Regards,
Tom Ogilvy
"Doug Glancy" wrote in message
...
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
"Tom Ogilvy" wrote in message
...
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 ;;;
--
Regards,
Tom Ogilvy
"Doug Glancy" wrote in message
...
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
"Tom Ogilvy" wrote in message
...
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?
|