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?

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?

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?

Danke. Merci. Gracias. Thank you.
"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?

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

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

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...


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

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?

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.

--
Regards,
Tom Ogilvy

"broro183" wrote in
message ...

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...


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

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?

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"broro183" wrote
in
message ...

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...


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

? 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?

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?

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

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?