Can I check if all cells are displaying properly and not showing #######. I don't want to autofit all cells as a workaround.
Can I check if all cells are displaying properly and not showing #######. I don't want to autofit all cells as a workaround.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Don't know if this works in all cases
[vba]
activecell.text=left$("##############################",len(activecell.Text) )
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
[vba]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[/vba]
@XLD: Howdy Bob - slightest typo for the read only .Text...
[VBA] ActiveCell.Value=Left$("##############################",Len(ActiveCell.Text ))[/VBA]
Last edited by GTO; 11-05-2008 at 08:42 PM.
Well spotted, thanksOriginally Posted by GTO
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks both,
Mark, I think you spotted exactly what I was after.
Regards
Malcolm
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Posting Permissions
Reply With Quote
