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

[Maury Markowitz]

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 Deitrick]

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

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[Mike H]

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]

Mike,

I blame "A Word A Day" ;-)

Bernie
MS Excel MVP

 

[Maury Markowitz]

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

Thanks!

Maury

 

[Maury Markowitz]

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

 

[Bernie Deitrick]

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

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 Markowitz]

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