0

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.

Richard Briggs
  • 423
  • 4
  • 14
  • 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

1 Answers1

1

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.

Richard Briggs
  • 423
  • 4
  • 14