Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Counting number of rows with Cond. Formatting to avoid save problem (KB 215783)
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Counting number of rows with Cond. Formatting to avoid save problem (KB 215783)
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 On Feb 28, 8:46 am, "Chrisso" wrote: 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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Counting number of rows with Cond. Formatting to avoid save problem (KB 215783)
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) "Chrisso" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Counting number of rows with Cond. Formatting to avoid save proble
This should give you an accurate count:
Sub gsnu() Set rf = ActiveCell.SpecialCells(xlCellTypeAllFormatConditi ons) 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 -- Gary's Student gsnu200708 "Chrisso" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of rows | New Users to Excel | |||
Counting number of rows | New Users to Excel | |||
Counting number of rows that are not N/A | Excel Programming | |||
Avoid counting rows/columns with formula but no values | Excel Programming | |||
Counting the number of rows | Excel Worksheet Functions |