Can I find columns with #### in them?
 

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

Can I find columns with #### in them?
 

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

Can I find columns with #### in them?
 

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

Can I find columns with #### in them?
 

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

Can I find columns with #### in them?
 

Mike,

I blame "A Word A Day" ;-)

Bernie
MS Excel MVP


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

Can I find columns with #### in them?
 

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

Thanks!

Maury

Can I find columns with #### in them?
 

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

Can I find columns with #### in them?
 

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

Can I find columns with #### in them?
 

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