Setting column width if #### appears in column

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" <Dkline001@comcast.net> wrote in message
news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
> for each cell in Range("A2:Z2")
> if instr(1,cell.Text,"###",vbTextCompare) then
> cell.EntireColumn.Autofit
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "qwerty" <Dkline001@comcast.net> wrote in message
> news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
>> 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" <twogilvy@msn.com> wrote in message
news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
> for each cell in Range("A2:Z2")
> if instr(1,cell.Text,"###",vbTextCompare) then
> cell.EntireColumn.Autofit
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "qwerty" <Dkline001@comcast.net> wrote in message
> news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
>> 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...

Originally Posted by Tom Ogilvy

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

--
Regards,
Tom Ogilvy



"qwerty" <Dkline001@comcast.net> wrote in message
news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
> 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?
>
>

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" <broro183.25ytxm_1144533301.5153@excelforum-nospam.com> wrote in
message news:broro183.25ytxm_1144533301.5153@excelforum-nospam.com...
>
> 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" <Dkline001@comcast.net> wrote in message
> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
> > > 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...

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" <nobodyhere@replytogroup.com> wrote in message
news:ebP1Hl0WGHA.3660@TK2MSFTNGP04.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
> > for each cell in Range("A2:Z2")
> > if instr(1,cell.Text,"###",vbTextCompare) then
> > cell.EntireColumn.Autofit
> > end if
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "qwerty" <Dkline001@comcast.net> wrote in message
> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
> >> 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" <broro183.25z2ym_1144545005.5679@excelforum-nospam.com> wrote in
message news:broro183.25z2ym_1144545005.5679@excelforum-nospam.com...
>
> 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" <twogilvy@msn.com> wrote in message
news:%23x1%23Kb3WGHA.3448@TK2MSFTNGP03.phx.gbl...
> 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" <nobodyhere@replytogroup.com> wrote in message
> news:ebP1Hl0WGHA.3660@TK2MSFTNGP04.phx.gbl...
>> 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" <twogilvy@msn.com> wrote in message
>> news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
>> > for each cell in Range("A2:Z2")
>> > if instr(1,cell.Text,"###",vbTextCompare) then
>> > cell.EntireColumn.Autofit
>> > end if
>> > Next
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "qwerty" <Dkline001@comcast.net> wrote in message
>> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
>> >> 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" <twogilvy@msn.com> wrote in message
news:ux8qRo3WGHA.4148@TK2MSFTNGP03.phx.gbl...
> 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" <broro183.25z2ym_1144545005.5679@excelforum-nospam.com> wrote
> in
> message news:broro183.25z2ym_1144545005.5679@excelforum-nospam.com...
>>
>> 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" <nobodyhere@replytogroup.com> wrote in message
news:en9zmX4WGHA.3332@TK2MSFTNGP02.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:%23x1%23Kb3WGHA.3448@TK2MSFTNGP03.phx.gbl...
> > 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" <nobodyhere@replytogroup.com> wrote in message
> > news:ebP1Hl0WGHA.3660@TK2MSFTNGP04.phx.gbl...
> >> 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" <twogilvy@msn.com> wrote in message
> >> news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
> >> > for each cell in Range("A2:Z2")
> >> > if instr(1,cell.Text,"###",vbTextCompare) then
> >> > cell.EntireColumn.Autofit
> >> > end if
> >> > Next
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> >
> >> > "qwerty" <Dkline001@comcast.net> wrote in message
> >> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
> >> >> 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" <twogilvy@msn.com> wrote in message
news:%23Ttaiy4WGHA.3448@TK2MSFTNGP03.phx.gbl...
> ? 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" <nobodyhere@replytogroup.com> wrote in message
> news:en9zmX4WGHA.3332@TK2MSFTNGP02.phx.gbl...
> > 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" <twogilvy@msn.com> wrote in message
> > news:%23x1%23Kb3WGHA.3448@TK2MSFTNGP03.phx.gbl...
> > > 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" <nobodyhere@replytogroup.com> wrote in message
> > > news:ebP1Hl0WGHA.3660@TK2MSFTNGP04.phx.gbl...
> > >> 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" <twogilvy@msn.com> wrote in message
> > >> news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
> > >> > for each cell in Range("A2:Z2")
> > >> > if instr(1,cell.Text,"###",vbTextCompare) then
> > >> > cell.EntireColumn.Autofit
> > >> > end if
> > >> > Next
> > >> >
> > >> > --
> > >> > Regards,
> > >> > Tom Ogilvy
> > >> >
> > >> >
> > >> >
> > >> > "qwerty" <Dkline001@comcast.net> wrote in message
> > >> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
> > >> >> 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" <twogilvy@msn.com> wrote in message
news:uAcyMS2WGHA.3684@TK2MSFTNGP05.phx.gbl...
> 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" <broro183.25ytxm_1144533301.5153@excelforum-nospam.com> wrote
> in
> message news:broro183.25ytxm_1144533301.5153@excelforum-nospam.com...
>>
>> 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" <Dkline001@comcast.net> wrote in message
>> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
>> > > 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" <twogilvy@msn.com> wrote in message
news:OaBWv64WGHA.1348@TK2MSFTNGP05.phx.gbl...
> OK. See you later post below.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:%23Ttaiy4WGHA.3448@TK2MSFTNGP03.phx.gbl...
>> ? 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" <nobodyhere@replytogroup.com> wrote in message
>> news:en9zmX4WGHA.3332@TK2MSFTNGP02.phx.gbl...
>> > 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" <twogilvy@msn.com> wrote in message
>> > news:%23x1%23Kb3WGHA.3448@TK2MSFTNGP03.phx.gbl...
>> > > 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" <nobodyhere@replytogroup.com> wrote in message
>> > > news:ebP1Hl0WGHA.3660@TK2MSFTNGP04.phx.gbl...
>> > >> 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" <twogilvy@msn.com> wrote in message
>> > >> news:%235zSBJ0WGHA.3672@TK2MSFTNGP02.phx.gbl...
>> > >> > for each cell in Range("A2:Z2")
>> > >> > if instr(1,cell.Text,"###",vbTextCompare) then
>> > >> > cell.EntireColumn.Autofit
>> > >> > end if
>> > >> > Next
>> > >> >
>> > >> > --
>> > >> > Regards,
>> > >> > Tom Ogilvy
>> > >> >
>> > >> >
>> > >> >
>> > >> > "qwerty" <Dkline001@comcast.net> wrote in message
>> > >> > news:uvTMf9zWGHA.128@TK2MSFTNGP05.phx.gbl...
>> > >> >> 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?
>> > >> >>
>> > >> >>
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>>
>
>