Counting number of rows with Cond. Formatting to avoid save problem (KB 215783)

[Chrisso]

Hi Chaps

I have hit the problem where I get the following message when I save
my large, old xls:

"Excel could not save all the data and formatting you recently added
to <my file>.xls"

I have read through these groups and the MS KB and it seems the only
reason this would happen is if I exceed 2050 rows of conditional
formatting.

I have written some code to try determine how many rows I have
conditional formatting - but this tells me I have only 1772 rows.

My code is below - can anyone see anything wrong with it which may be
under reporting the number of lines with CF? Does anyone have any
other similar utilties to attack this problem?

Otherwise this cond. formatting limitation may not be my problem- does
anyone have any ideas on other possible causes?

Many thanks for any ideas.
Chrisso

Sub CF2050_Report_All_Sheets()
Application.ScreenUpdating = False

Dim totalCount, thisRowCount, thisSheetCount As Long
Dim cell As Range
Dim wrkSheet As Worksheet
Dim report As String

For Each wrkSheet In Worksheets
wrkSheet.Activate
thisSheetCount = 0

' determine the last cell - no need to look past it
Dim lastCell As Range
Set lastCell = Range("A1").SpecialCells(xlLastCell)
' loop through each row till the last cell
For myRow = 1 To lastCell.Row
thisRowCount = 0
' only need to look as far as the last cell column
For Each cell In Range(Cells(myRow, 1), Cells(myRow,
lastCell.Column))
If cell.FormatConditions.Count > 0 Then thisRowCount =
thisRowCount + 1
Next
If thisRowCount > 0 Then thisSheetCount = thisSheetCount +
1
Next myRow

' add to our count and report
totalCount = totalCount + thisSheetCount
report = report & wrkSheet.Name & ": " & thisSheetCount & vbCr
Next

Application.ScreenUpdating = True
MsgBox "Total Count of Rows With CF: " & totalCount & vbCr & vbCr
& report
End Sub

 

[Susan]

maybe it isn't JUST conditional formatting that is too much..........

You receive a "Too many different cell formats" error message in Excel
View products that this article applies to.
Article ID : 213904

SYMPTOMS
In Microsoft Excel, when you format a cell or a range of cells, you
may receive the following error message:
Too many different cell formats.
Back to the top

CAUSE
This problem occurs when the workbook contains more than approximately
4,000 different combinations of cell formats. A combination is defined
as a unique set of formatting elements that are applied to a cell. A
combination includes all font formatting (for example: typeface, font
size, italic, bold, and underline), borders (for example: location,
weight, and color), cell patterns, number formatting, alignment, and
cell protection.

NOTE: If two or more cells share exactly the same formatting, they use
one formatting combination. However, if there are any differences in
formatting between the cells, each cell uses a different combination.

RESOLUTION
To resolve this problem, simplify the formatting in the workbook. For
example, the following are suggestions for simplifying formatting: ·
Use a standard font.

Using the same font for all cells reduces the number of formatting
combinations.
· If you use borders in a worksheet, use them consistently.

NOTE: If you apply a border to the right side of a cell, it is not
necessary to apply a border to the left side of the cell that is to
the right because the borders overlap.
· If you apply patterns to the cells, remove the patterns by clicking
No Color in the Patterns tab of the Format Cells dialog box.
NOTE: After you simplify or standardize the formatting in the
workbook, save, close, and then reopen the workbook before you apply
additional cell formatting.

MORE INFORMATION
In most cases, the limit of approximately 4,000 different formatting
combinations for a single workbook is sufficient. This problem is
likely to occur only when the workbook contains a large number of
worksheets that use different formatting, or when a large number of
cells are all formatted differently.

i saved this - it was linked from a previous post.....
http://support.microsoft.com/kb/213904
maybe this will help.
susan

 

[Bob Phillips]

Not properly qualifying ranges

Sub CF2050_Report_All_Sheets()
Application.ScreenUpdating = False

Dim totalCount As Long, thisSheetCount As Long
Dim cell As Range
Dim wrkSheet As Worksheet
Dim report As String
Dim lastCell As Range

For Each wrkSheet In Worksheets
wrkSheet.Activate
thisSheetCount = 0

' determine the last cell - no need to look past it
Set lastCell = .Range("A1").SpecialCells(xlLastCell)
' loop through each row till the last cell
For Each cell In .Range(Range("A1"), lastCell)
If cell.FormatConditions.Count > 0 Then _
thisSheetCount = thisSheetCount + 1
Next cell

' add to our count and report
report = report & wrkSheet.Name & ": " & thisSheetCount & vbCr
totalCount = totalCount + thisSheetCount
Next

Application.ScreenUpdating = True
MsgBox "Total Count of Rows With CF: " & _
totalCount & vbCr & vbCr & report
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

This should give you an accurate count:

Sub gsnu()
Set rf = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
rcount = 0
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 1 To nLastRow
Set rr = Rows(i)
If Intersect(rr, rf) Is Nothing Then
Else
rcount = rcount + 1
End If
Next
MsgBox (rcount)
End Sub