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

--
Regards,
Tom Ogilvy



"qwerty" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

[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


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> for each cell in Range("A2:Z2")
> if instr(1,cell.Text,"###",vbTextCompare) then
> cell.EntireColumn.Autofit
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "qwerty" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

[qwerty]

Danke. Merci. Gracias. Thank you.
"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> for each cell in Range("A2:Z2")
> if instr(1,cell.Text,"###",vbTextCompare) then
> cell.EntireColumn.Autofit
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "qwerty" <[email protected]> wrote in message
> news:[email protected]...
>> 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]

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

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

--
Regards,
Tom Ogilvy



"qwerty" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
>

[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 ;;;

--
Regards,
Tom Ogilvy


"Doug Glancy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > for each cell in Range("A2:Z2")
> > if instr(1,cell.Text,"###",vbTextCompare) then
> > cell.EntireColumn.Autofit
> > end if
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "qwerty" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 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

"Tom Ogilvy" <[email protected]> wrote in message
news:%23x1%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > for each cell in Range("A2:Z2")
>> > if instr(1,cell.Text,"###",vbTextCompare) then
>> > cell.EntireColumn.Autofit
>> > end if
>> > Next
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "qwerty" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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?
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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 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. .

--
Regards,
Tom Ogilvy



"Doug Glancy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23x1%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > for each cell in Range("A2:Z2")
> >> > if instr(1,cell.Text,"###",vbTextCompare) then
> >> > cell.EntireColumn.Autofit
> >> > end if
> >> > Next
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> >
> >> > "qwerty" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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]

OK. See you later post below.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> ? 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%23x1%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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" <[email protected]> wrote in message
> > >> news:%[email protected]...
> > >> > for each cell in Range("A2:Z2")
> > >> > if instr(1,cell.Text,"###",vbTextCompare) then
> > >> > cell.EntireColumn.Autofit
> > >> > end if
> > >> > Next
> > >> >
> > >> > --
> > >> > Regards,
> > >> > Tom Ogilvy
> > >> >
> > >> >
> > >> >
> > >> > "qwerty" <[email protected]> wrote in message
> > >> > news:[email protected]...
> > >> >> 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?
> > >> >>
> > >> >>
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>

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

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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
>>
>
>

[Doug Glancy]

Thanks Tom.

Doug


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> OK. See you later post below.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
>> ? 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:%23x1%[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > >> 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" <[email protected]> wrote in message
>> > >> news:%[email protected]...
>> > >> > for each cell in Range("A2:Z2")
>> > >> > if instr(1,cell.Text,"###",vbTextCompare) then
>> > >> > cell.EntireColumn.Autofit
>> > >> > end if
>> > >> > Next
>> > >> >
>> > >> > --
>> > >> > Regards,
>> > >> > Tom Ogilvy
>> > >> >
>> > >> >
>> > >> >
>> > >> > "qwerty" <[email protected]> wrote in message
>> > >> > news:[email protected]...
>> > >> >> 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?
>> > >> >>
>> > >> >>
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>>
>
>