Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting text fits into column
How would I go about writing code to check an entire spreadsheet (every tab,
every cell that is not blank) for text/values that do not fir into a cell (and are therefore not fully displayed. Also, to check that the same font is used in the same tab. Once this has been detected I would like to automatically create a summary sheet that lists the location of cells that are not fully displayed. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting text fits into column
The following will work with text and values which are not the result of
formulae: Sub RTP() ' ' gsnuxx ' j = 0 Set r1 = Sheets("sumry").Range("A1") For Each w In Worksheets If w.Name = "sumry" Then Else w.Activate n = w.Name For Each r In ActiveSheet.UsedRange If IsEmpty(r.Value) Then Else r.Copy r1 w = r.Columns.Width h = r.Rows.Height r1.Columns.ColumnWidth = w r1.Rows.RowHeight = h r1.Columns.EntireColumn.AutoFit If w < r1.Columns.Width Then r1.Offset(j, 1).Value = n & " " & r.Address j = j + 1 End If End If Next End If Next The sub uses a "helper" sheet called "sumry". Cells are copied to cell A1 in "sumry" along with the cell's dimensions. Column A in "sumry" is then autofit. If the column grew, then the original contents did not fit and the results are recorded in column B. This process is performed for all sheets and all cells within the sheets. -- Gary's Student gsnu200708 "RTP" wrote: How would I go about writing code to check an entire spreadsheet (every tab, every cell that is not blank) for text/values that do not fir into a cell (and are therefore not fully displayed. Also, to check that the same font is used in the same tab. Once this has been detected I would like to automatically create a summary sheet that lists the location of cells that are not fully displayed. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting text and returning a value | Excel Discussion (Misc queries) | |||
How do I list all Data in a column that fits between two points? | Excel Discussion (Misc queries) | |||
Formula for detecting a certain pattern in a particular row or column? | Excel Programming | |||
Detecting if a value "fits" in a cell. | Excel Programming | |||
detecting row or column insertion & deletion | Excel Programming |