|
How do I delete all rows that contain no red highlighted data?
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? |
How do I delete all rows that contain no 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
How do I delete all rows that contain no 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. "Stefi" wrote: 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
How do I delete all rows that contain no 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 & ":" & rownum) If cella.Font.ColorIndex = colorcode Then ColoredRow = True Exit Function End If Next cella End Function Regards, Stefi €˛Linda Hudock€¯ ezt Ć*rta: 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. "Stefi" wrote: 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
How do I delete all rows that contain no red highlighted data?
This is just too wonderful for words! It saved me hours of work!!! I can't
thank you enough! "Stefi" wrote: 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 & ":" & rownum) If cella.Font.ColorIndex = colorcode Then ColoredRow = True Exit Function End If Next cella End Function Regards, Stefi €˛Linda Hudock€¯ ezt Ć*rta: 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. "Stefi" wrote: 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
How do I delete all rows that contain no red highlighted data?
You are welcome, Linda! Thanks for the feedback!
Stefi €˛Linda Hudock€¯ ezt Ć*rta: This is just too wonderful for words! It saved me hours of work!!! I can't thank you enough! "Stefi" wrote: 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 & ":" & rownum) If cella.Font.ColorIndex = colorcode Then ColoredRow = True Exit Function End If Next cella End Function Regards, Stefi €˛Linda Hudock€¯ ezt Ć*rta: 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. "Stefi" wrote: 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
How do I delete all rows that contain no 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. "Stefi" wrote: You are welcome, Linda! Thanks for the feedback! Stefi €˛Linda Hudock€¯ ezt Ć*rta: This is just too wonderful for words! It saved me hours of work!!! I can't thank you enough! "Stefi" wrote: 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 & ":" & rownum) If cella.Font.ColorIndex = colorcode Then ColoredRow = True Exit Function End If Next cella End Function Regards, Stefi €˛Linda Hudock€¯ ezt Ć*rta: 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. "Stefi" wrote: 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
How do I delete all rows that contain no 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 €˛Linda Hudock€¯ ezt Ć*rta: 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. "Stefi" wrote: You are welcome, Linda! Thanks for the feedback! Stefi €˛Linda Hudock€¯ ezt Ć*rta: This is just too wonderful for words! It saved me hours of work!!! I can't thank you enough! "Stefi" wrote: 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 & ":" & rownum) If cella.Font.ColorIndex = colorcode Then ColoredRow = True Exit Function End If Next cella End Function Regards, Stefi €˛Linda Hudock€¯ ezt Ć*rta: 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. "Stefi" wrote: 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 €˛Linda Hudock€¯ ezt Ć*rta: 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? |
| All times are GMT +1. The time now is 08:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com