software

I have a LARGE spreadsheet in which have highlighted erroneous data in red.
I would like to know how to quickly and easily delete any row in the sheet
that does not have red highlighted data in it so that I can count the number
of records that have erroneous data using subtotals. Or, is there is a
better way to do this than deleting the rows w/o red highlighted data?

Hi Linda,

The way depends on HOW are the erroneous cells highlighted: is their font or
background color manually set to red or is a conditional formatting applied
to them?
The font or the background color is red?
Are the erroneous cells in the same column or are they in different columns?

Regards,
Stefi

?inda Hudock??ezt ?rta:

gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; I would like to know how to quickly and easily delete any row in the sheet
gt; that does not have red highlighted data in it so that I can count the number
gt; of records that have erroneous data using subtotals. Or, is there is a
gt; better way to do this than deleting the rows w/o red highlighted data?

The cells are MANUALLY set (not conditionally set). The FONT is colored red
(not the background). There are several columns. The row can have red
colored errors in any of the columns. If there is one or more red colored
errors in the row (ie, in any column), I want to keep the row. If there are
no red colored cells in any column in the row, I want to delete the entire
row.

quot;Stefiquot; wrote:

gt; Hi Linda,
gt;
gt; The way depends on HOW are the erroneous cells highlighted: is their font or
gt; background color manually set to red or is a conditional formatting applied
gt; to them?
gt; The font or the background color is red?
gt; Are the erroneous cells in the same column or are they in different columns?
gt;
gt; Regards,
gt; Stefi
gt;
gt; ?inda Hudock??ezt ?rta:
gt;
gt; gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; gt; I would like to know how to quickly and easily delete any row in the sheet
gt; gt; that does not have red highlighted data in it so that I can count the number
gt; gt; of records that have erroneous data using subtotals. Or, is there is a
gt; gt; better way to do this than deleting the rows w/o red highlighted data?

Install this UDF (post if you need assistance in installing), enter
=ColoredRow(ROW(),3) in all rows in an unused column! Autofilter the TRUE
values in this column, and copy them into a separate sheet!

Function ColoredRow(rownum, colorcode)
ColoredRow = False
For Each cella In Range(rownum amp; quot;:quot; amp; rownum)
If cella.Font.ColorIndex = colorcode Then
ColoredRow = True
Exit Function
End If
Next cella
End Function

Regards,
Stefi?inda Hudock??ezt ?rta:

gt; The cells are MANUALLY set (not conditionally set). The FONT is colored red
gt; (not the background). There are several columns. The row can have red
gt; colored errors in any of the columns. If there is one or more red colored
gt; errors in the row (ie, in any column), I want to keep the row. If there are
gt; no red colored cells in any column in the row, I want to delete the entire
gt; row.
gt;
gt; quot;Stefiquot; wrote:
gt;
gt; gt; Hi Linda,
gt; gt;
gt; gt; The way depends on HOW are the erroneous cells highlighted: is their font or
gt; gt; background color manually set to red or is a conditional formatting applied
gt; gt; to them?
gt; gt; The font or the background color is red?
gt; gt; Are the erroneous cells in the same column or are they in different columns?
gt; gt;
gt; gt; Regards,
gt; gt; Stefi
gt; gt;
gt; gt; ?inda Hudock??ezt ?rta:
gt; gt;
gt; gt; gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; gt; gt; I would like to know how to quickly and easily delete any row in the sheet
gt; gt; gt; that does not have red highlighted data in it so that I can count the number
gt; gt; gt; of records that have erroneous data using subtotals. Or, is there is a
gt; gt; gt; better way to do this than deleting the rows w/o red highlighted data?

This is just too wonderful for words! It saved me hours of work!!! I can't
thank you enough!quot;Stefiquot; wrote:

gt; Install this UDF (post if you need assistance in installing), enter
gt; =ColoredRow(ROW(),3) in all rows in an unused column! Autofilter the TRUE
gt; values in this column, and copy them into a separate sheet!
gt;
gt; Function ColoredRow(rownum, colorcode)
gt; ColoredRow = False
gt; For Each cella In Range(rownum amp; quot;:quot; amp; rownum)
gt; If cella.Font.ColorIndex = colorcode Then
gt; ColoredRow = True
gt; Exit Function
gt; End If
gt; Next cella
gt; End Function
gt;
gt; Regards,
gt; Stefi
gt;
gt;
gt; ?inda Hudock??ezt ?rta:
gt;
gt; gt; The cells are MANUALLY set (not conditionally set). The FONT is colored red
gt; gt; (not the background). There are several columns. The row can have red
gt; gt; colored errors in any of the columns. If there is one or more red colored
gt; gt; errors in the row (ie, in any column), I want to keep the row. If there are
gt; gt; no red colored cells in any column in the row, I want to delete the entire
gt; gt; row.
gt; gt;
gt; gt; quot;Stefiquot; wrote:
gt; gt;
gt; gt; gt; Hi Linda,
gt; gt; gt;
gt; gt; gt; The way depends on HOW are the erroneous cells highlighted: is their font or
gt; gt; gt; background color manually set to red or is a conditional formatting applied
gt; gt; gt; to them?
gt; gt; gt; The font or the background color is red?
gt; gt; gt; Are the erroneous cells in the same column or are they in different columns?
gt; gt; gt;
gt; gt; gt; Regards,
gt; gt; gt; Stefi
gt; gt; gt;
gt; gt; gt; ?inda Hudock??ezt ?rta:
gt; gt; gt;
gt; gt; gt; gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; gt; gt; gt; I would like to know how to quickly and easily delete any row in the sheet
gt; gt; gt; gt; that does not have red highlighted data in it so that I can count the number
gt; gt; gt; gt; of records that have erroneous data using subtotals. Or, is there is a
gt; gt; gt; gt; better way to do this than deleting the rows w/o red highlighted data?

You are welcome, Linda! Thanks for the feedback!
Stefi?inda Hudock??ezt ?rta:

gt; This is just too wonderful for words! It saved me hours of work!!! I can't
gt; thank you enough!
gt;
gt;
gt; quot;Stefiquot; wrote:
gt;
gt; gt; Install this UDF (post if you need assistance in installing), enter
gt; gt; =ColoredRow(ROW(),3) in all rows in an unused column! Autofilter the TRUE
gt; gt; values in this column, and copy them into a separate sheet!
gt; gt;
gt; gt; Function ColoredRow(rownum, colorcode)
gt; gt; ColoredRow = False
gt; gt; For Each cella In Range(rownum amp; quot;:quot; amp; rownum)
gt; gt; If cella.Font.ColorIndex = colorcode Then
gt; gt; ColoredRow = True
gt; gt; Exit Function
gt; gt; End If
gt; gt; Next cella
gt; gt; End Function
gt; gt;
gt; gt; Regards,
gt; gt; Stefi
gt; gt;
gt; gt;
gt; gt; ?inda Hudock??ezt ?rta:
gt; gt;
gt; gt; gt; The cells are MANUALLY set (not conditionally set). The FONT is colored red
gt; gt; gt; (not the background). There are several columns. The row can have red
gt; gt; gt; colored errors in any of the columns. If there is one or more red colored
gt; gt; gt; errors in the row (ie, in any column), I want to keep the row. If there are
gt; gt; gt; no red colored cells in any column in the row, I want to delete the entire
gt; gt; gt; row.
gt; gt; gt;
gt; gt; gt; quot;Stefiquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi Linda,
gt; gt; gt; gt;
gt; gt; gt; gt; The way depends on HOW are the erroneous cells highlighted: is their font or
gt; gt; gt; gt; background color manually set to red or is a conditional formatting applied
gt; gt; gt; gt; to them?
gt; gt; gt; gt; The font or the background color is red?
gt; gt; gt; gt; Are the erroneous cells in the same column or are they in different columns?
gt; gt; gt; gt;
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Stefi
gt; gt; gt; gt;
gt; gt; gt; gt; ?inda Hudock??ezt ?rta:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; gt; gt; gt; gt; I would like to know how to quickly and easily delete any row in the sheet
gt; gt; gt; gt; gt; that does not have red highlighted data in it so that I can count the number
gt; gt; gt; gt; gt; of records that have erroneous data using subtotals. Or, is there is a
gt; gt; gt; gt; gt; better way to do this than deleting the rows w/o red highlighted data?

Another question... Do I have to install the UDF each time I create a new
spreadsheet? I had to do so this time. Unless I did something wrong in the
installation? When I copied/pasted the formula into each cell of the new
spreadsheet, I got NAME? untill I installed the UDF again.quot;Stefiquot; wrote:

gt; You are welcome, Linda! Thanks for the feedback!
gt; Stefi
gt;
gt;
gt; ?inda Hudock??ezt ?rta:
gt;
gt; gt; This is just too wonderful for words! It saved me hours of work!!! I can't
gt; gt; thank you enough!
gt; gt;
gt; gt;
gt; gt; quot;Stefiquot; wrote:
gt; gt;
gt; gt; gt; Install this UDF (post if you need assistance in installing), enter
gt; gt; gt; =ColoredRow(ROW(),3) in all rows in an unused column! Autofilter the TRUE
gt; gt; gt; values in this column, and copy them into a separate sheet!
gt; gt; gt;
gt; gt; gt; Function ColoredRow(rownum, colorcode)
gt; gt; gt; ColoredRow = False
gt; gt; gt; For Each cella In Range(rownum amp; quot;:quot; amp; rownum)
gt; gt; gt; If cella.Font.ColorIndex = colorcode Then
gt; gt; gt; ColoredRow = True
gt; gt; gt; Exit Function
gt; gt; gt; End If
gt; gt; gt; Next cella
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; Regards,
gt; gt; gt; Stefi
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; ?inda Hudock??ezt ?rta:
gt; gt; gt;
gt; gt; gt; gt; The cells are MANUALLY set (not conditionally set). The FONT is colored red
gt; gt; gt; gt; (not the background). There are several columns. The row can have red
gt; gt; gt; gt; colored errors in any of the columns. If there is one or more red colored
gt; gt; gt; gt; errors in the row (ie, in any column), I want to keep the row. If there are
gt; gt; gt; gt; no red colored cells in any column in the row, I want to delete the entire
gt; gt; gt; gt; row.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Stefiquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi Linda,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The way depends on HOW are the erroneous cells highlighted: is their font or
gt; gt; gt; gt; gt; background color manually set to red or is a conditional formatting applied
gt; gt; gt; gt; gt; to them?
gt; gt; gt; gt; gt; The font or the background color is red?
gt; gt; gt; gt; gt; Are the erroneous cells in the same column or are they in different columns?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Stefi
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ?inda Hudock??ezt ?rta:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; gt; gt; gt; gt; gt; I would like to know how to quickly and easily delete any row in the sheet
gt; gt; gt; gt; gt; gt; that does not have red highlighted data in it so that I can count the number
gt; gt; gt; gt; gt; gt; of records that have erroneous data using subtotals. Or, is there is a
gt; gt; gt; gt; gt; gt; better way to do this than deleting the rows w/o red highlighted data?

Hi Linda,

You have two ways of using an UDF in different workbooks:
1. Installing it in all new workbooks as you did it.
2. Installing it once in your Personal.xls, then it will be accessible for
all your workbooks. In this case refer to the UDF like
Personal.xls!ColoredRow(ROW(),3)

Regards,
Stefi

?inda Hudock??ezt ?rta:

gt; Another question... Do I have to install the UDF each time I create a new
gt; spreadsheet? I had to do so this time. Unless I did something wrong in the
gt; installation? When I copied/pasted the formula into each cell of the new
gt; spreadsheet, I got NAME? untill I installed the UDF again.
gt;
gt;
gt; quot;Stefiquot; wrote:
gt;
gt; gt; You are welcome, Linda! Thanks for the feedback!
gt; gt; Stefi
gt; gt;
gt; gt;
gt; gt; ?inda Hudock??ezt ?rta:
gt; gt;
gt; gt; gt; This is just too wonderful for words! It saved me hours of work!!! I can't
gt; gt; gt; thank you enough!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Stefiquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Install this UDF (post if you need assistance in installing), enter
gt; gt; gt; gt; =ColoredRow(ROW(),3) in all rows in an unused column! Autofilter the TRUE
gt; gt; gt; gt; values in this column, and copy them into a separate sheet!
gt; gt; gt; gt;
gt; gt; gt; gt; Function ColoredRow(rownum, colorcode)
gt; gt; gt; gt; ColoredRow = False
gt; gt; gt; gt; For Each cella In Range(rownum amp; quot;:quot; amp; rownum)
gt; gt; gt; gt; If cella.Font.ColorIndex = colorcode Then
gt; gt; gt; gt; ColoredRow = True
gt; gt; gt; gt; Exit Function
gt; gt; gt; gt; End If
gt; gt; gt; gt; Next cella
gt; gt; gt; gt; End Function
gt; gt; gt; gt;
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Stefi
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; ?inda Hudock??ezt ?rta:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; The cells are MANUALLY set (not conditionally set). The FONT is colored red
gt; gt; gt; gt; gt; (not the background). There are several columns. The row can have red
gt; gt; gt; gt; gt; colored errors in any of the columns. If there is one or more red colored
gt; gt; gt; gt; gt; errors in the row (ie, in any column), I want to keep the row. If there are
gt; gt; gt; gt; gt; no red colored cells in any column in the row, I want to delete the entire
gt; gt; gt; gt; gt; row.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Stefiquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Hi Linda,
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; The way depends on HOW are the erroneous cells highlighted: is their font or
gt; gt; gt; gt; gt; gt; background color manually set to red or is a conditional formatting applied
gt; gt; gt; gt; gt; gt; to them?
gt; gt; gt; gt; gt; gt; The font or the background color is red?
gt; gt; gt; gt; gt; gt; Are the erroneous cells in the same column or are they in different columns?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; Stefi
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; ?inda Hudock??ezt ?rta:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I have a LARGE spreadsheet in which have highlighted erroneous data in red.
gt; gt; gt; gt; gt; gt; gt; I would like to know how to quickly and easily delete any row in the sheet
gt; gt; gt; gt; gt; gt; gt; that does not have red highlighted data in it so that I can count the number
gt; gt; gt; gt; gt; gt; gt; of records that have erroneous data using subtotals. Or, is there is a
gt; gt; gt; gt; gt; gt; gt; better way to do this than deleting the rows w/o red highlighted data?