View Full Version : Solved: Test for correct display.
mdmackillop
11-05-2008, 01:20 PM
Can I check if all cells are displaying properly and not showing #######. I don't want to autofit all cells as a workaround.
Bob Phillips
11-05-2008, 01:58 PM
Don't know if this works in all cases
activecell.text=left$("##############################",len(activecell.Text))
Hi Malcom,
I am guessing a teeny bit, as to whether you'd be looking to autofit just the columns that this occurred in, or autosize the font or ...
Anyways, would this be of help?
Have a great day,
Mark
Function TooSkinny()
Dim _
wksActive As Worksheet, _
rngUsed As Range, _
rngCol As Range, _
rngRow As Range
Set wksActive = ActiveSheet
Set rngUsed = wksActive.UsedRange
For Each rngCol In rngUsed.Columns
For Each rngRow In rngCol.Rows
If (rngRow.Text Like "[#]*" _
And Not rngRow.Value Like "[#]*") _
Or _
(rngRow.Text Like "*1E+*" _
And Not rngRow.Value Like "*1E+*") Then
rngCol.AutoFit
Exit For
End If
Next
Next
Set wksActive = Nothing
Set rngUsed = Nothing
End Function
@XLD: Howdy Bob - slightest typo for the read only .Text...
ActiveCell.Value=Left$("##############################",Len(ActiveCell.Text))
Bob Phillips
11-06-2008, 01:15 AM
@XLD: Howdy Bob - slightest typo for the read only .Text...
ActiveCell.Value=Left$("##############################",Len(ActiveCell.Text))
Well spotted, thanks
mdmackillop
11-06-2008, 01:29 AM
Thanks,
I'll have a look at these. Some of the forms I have to use are very tight for printing width and need balancing of columns. By knowing if autofit is required, I can adjust elsewhere for particular occasions. ie If Column G increases by 2 points, then reduce D by 2 points and restore after printing.
Regards
Malcolm
mdmackillop
11-06-2008, 11:23 AM
Thanks both,
Mark, I think you spotted exactly what I was after.
Regards
Malcolm
Thanks both,
Mark, I think you spotted exactly what I was after.
Regards
Malcolm
Good Midday,
While of course you are most welcome, I would feel a bit remiss in not expressing that you have certainly helped me to a far greater degree; not only in catching a 'glitch' or two of mine, but in code you have provided others (that I have learned from).
The best to you and yours,
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.