Excel Find cell displaying ######

Question

I would like to find any cell where the value has overflowed it's column width.

Looking at a cell's properties I can see that the cell Text value is ####, but the Excel Find method does not seem to allow me to search anything other than the cell Value and the cell Formula.

What data is actually in your sell? most likely the width of column is not enough - for these cases cell is visible as ######### — Peter L., Feb 12 '13 at 12:59
Try this: http://stackoverflow.com/questions/10930743/how-to-detect-values-that-do-not-fit-in-excel-cells-using-vba Searchbar is your Friend — Smartis has left SO again, Feb 12 '13 at 13:01
I am writing a macro to find cells that have overflowed, the macro will then autofit the cell. — Richard Briggs, Feb 12 '13 at 13:02
I looked at the thread you linked to, it will not work for me as my sheet is very large and I wish to avoid calling autofit unless it is necessary, as it slows the application down too much. — Richard Briggs, Feb 12 '13 at 13:06
Maybe something like: iterate over columns; at each column get column width; iterate down rows: find text length of each cell; when reach bottom of column, set column width to 2) if max length > some number you know will overflow, do autofit or set width to overflow width + , say, 1 or 2) set max text length to max width of text + 1 or 2 or 3) go to next column — chuff, Feb 12 '13 at 14:33
Possible duplicate: http://stackoverflow.com/questions/2105361/find-text-cells-that-are-too-small-to-display-contents — StoriKnow, Feb 12 '13 at 18:37

score 1 · Accepted Answer · answered Feb 12 '13 at 22:45

I got it finally -

Set hashrange = .Cells.Find(What:="##", After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not hashrange Is Nothing Then  
    hashrange.Columns.AutoFit
End If

This not a duplicate post as suggested, the solution I was after was the fastest method of finding overflowed cells without iterating rows or columns in vba - which is too slow (for me) in large sheets.

Hope someone finds this useful - using this in my app now Excel Database Tasks

Thank you very much for all responses.

Excel Find cell displaying ######

1 Answers1