I like autofit, but sometimes you want to hand-size a column. But then
when the numbers overflow, it's nice to be able to jiggle them.

But how do I find which columns are not wide enough? Is there a trick?

Maury

Maury,

You need to use the Text property of the range object:


Sub FindHashes()
Dim myC As Range
For Each myC In Range("A1:C10") 'Change to suit
If InStr(1, myC.Text, "#") > 0 Then
MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value
End If
Next myC

End Sub

HTH,
Bernie
MS Excel MVP


"Maury Markowitz" <(E-Mail Removed)> wrote in message
news:d3fd0110-971c-4261-8f5e-(E-Mail Removed)...
>I like autofit, but sometimes you want to hand-size a column. But then
> when the numbers overflow, it's nice to be able to jiggle them.
>
> But how do I find which columns are not wide enough? Is there a trick?
>
> Maury

Of course, that will fail if your actual values have octothorps included.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:(E-Mail Removed)...
> Maury,
>
> You need to use the Text property of the range object:
>
>
> Sub FindHashes()
> Dim myC As Range
> For Each myC In Range("A1:C10") 'Change to suit
> If InStr(1, myC.Text, "#") > 0 Then
> MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value
> End If
> Next myC
>
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Maury Markowitz" <(E-Mail Removed)> wrote in message
> news:d3fd0110-971c-4261-8f5e-(E-Mail Removed)...
>>I like autofit, but sometimes you want to hand-size a column. But then
>> when the numbers overflow, it's nice to be able to jiggle them.
>>
>> But how do I find which columns are not wide enough? Is there a trick?
>>
>> Maury
>
>

Bernie,

Well that broadened my command of the english language!! I've never heard of
then called that before and in all honesty looked it up before responding.
Now how can I slip that into conversation??

Mike

"Bernie Deitrick" wrote:

> Of course, that will fail if your actual values have octothorps included.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:(E-Mail Removed)...
> > Maury,
> >
> > You need to use the Text property of the range object:
> >
> >
> > Sub FindHashes()
> > Dim myC As Range
> > For Each myC In Range("A1:C10") 'Change to suit
> > If InStr(1, myC.Text, "#") > 0 Then
> > MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value
> > End If
> > Next myC
> >
> > End Sub
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Maury Markowitz" <(E-Mail Removed)> wrote in message
> > news:d3fd0110-971c-4261-8f5e-(E-Mail Removed)...
> >>I like autofit, but sometimes you want to hand-size a column. But then
> >> when the numbers overflow, it's nice to be able to jiggle them.
> >>
> >> But how do I find which columns are not wide enough? Is there a trick?
> >>
> >> Maury
> >
> >
>
>
>

Mike,

I blame "A Word A Day" ;-)

Bernie
MS Excel MVP


"Mike H" <(E-Mail Removed)> wrote in message
news:C61943F2-5F9E-4450-A183-(E-Mail Removed)...
> Bernie,
>
> Well that broadened my command of the english language!! I've never heard of
> then called that before and in all honesty looked it up before responding.
> Now how can I slip that into conversation??
>
> Mike
>
> "Bernie Deitrick" wrote:
>
>> Of course, that will fail if your actual values have octothorps included.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:(E-Mail Removed)...
>> > Maury,
>> >
>> > You need to use the Text property of the range object:
>> >
>> >
>> > Sub FindHashes()
>> > Dim myC As Range
>> > For Each myC In Range("A1:C10") 'Change to suit
>> > If InStr(1, myC.Text, "#") > 0 Then
>> > MsgBox myC.Address & " has the text " & myC.Text & " instead of " & myC.Value
>> > End If
>> > Next myC
>> >
>> > End Sub
>> >
>> > HTH,
>> > Bernie
>> > MS Excel MVP
>> >
>> >
>> > "Maury Markowitz" <(E-Mail Removed)> wrote in message
>> > news:d3fd0110-971c-4261-8f5e-(E-Mail Removed)...
>> >>I like autofit, but sometimes you want to hand-size a column. But then
>> >> when the numbers overflow, it's nice to be able to jiggle them.
>> >>
>> >> But how do I find which columns are not wide enough? Is there a trick?
>> >>
>> >> Maury
>> >
>> >
>>
>>
>>

Wow, that should have been obvious in retrospect...

Thanks!

Maury

On Sep 29, 1:26*pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> For Each myC In Range("A1:C10") 'Change to suit

Wait, is something missing here? Shouldn't that be myC.something?
Perhaps I misunderstand the way this works?

Also, is this going to loop over every single cell in the sheet? Or
does .text do something clever?

Maury

Maury,

> Shouldn't that be myC.something?

No.

myC is a range object, so what those lines mean is

"for every cell in the cells of this range do this"

As written, the code will check only the 30 cells in the range A1:C10.

You can change that to

Do all the cells in a specific range:
For Each myC In Range("A11:H200")

Do all the cells in that have constants values:
For Each myC In Cells.SpecialCells(xlCellTypeConstants, 23)



Do all the cells in that have formulas:
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23)

and so on.

What cells do you want to check?

HTH,
Bernie
MS Excel MVP





"Maury Markowitz" <(E-Mail Removed)> wrote in message
news:2be14cf4-8784-45db-9d6f-(E-Mail Removed)...
On Sep 29, 1:26 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> For Each myC In Range("A1:C10") 'Change to suit

Wait, is something missing here? Shouldn't that be myC.something?
Perhaps I misunderstand the way this works?

Also, is this going to loop over every single cell in the sheet? Or
does .text do something clever?

Maury

On Sep 29, 11:24*pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> What cells do you want to check?

Oh it works fine, it was just my confusion over what Excel did if you
For Each over a range.

It seems to be reasonably fast too, that was my other concern, that
looping over every cell on my large sheets would be expensive.

Maury