Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
I have 80 rows of data and and one of the columns contains the serial number
of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
How did the font become red... manually colored or via a Conditional
Format? -- Rick (MVP - Excel) "Lee" wrote in message ... I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
It was manually colored not via conditional formating.
Regards, Lee "Rick Rothstein" wrote: How did the font become red... manually colored or via a Conditional Format? -- Rick (MVP - Excel) "Lee" wrote in message ... I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
Assuming they were manually colored red...
Sorry, but you will need a loop, but the good news is the loop will only execute as many times as there are serial numbers with red fonts (2 such cells and the loop only iterates 2 times). This macro will do that for manually colored text in Column A (change the "A" in the Columns property call inside the loop to what ever column has your serial numbers)... Sub DeleteRedFontRows() Dim UserResponse As Variant On Error GoTo NoRedFonts Application.ScreenUpdating = False Application.FindFormat.Font.ColorIndex = 3 Do Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete Loop NoRedFonts: Application.ScreenUpdating = True End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... How did the font become red... manually colored or via a Conditional Format? -- Rick (MVP - Excel) "Lee" wrote in message ... I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
Hmmm, that didn't work for me. Perhaps the problem is that I have a
different color of Red than ColorIndex 3. This works: Sub DeleteRowsWithRedFont() Dim row As Integer For i = 2 To 81 If Range(Cells(i, 1), Cells(i, 1)).Font.Color = RGB(255, 0, 0) Then Rows(i).Delete shift:=xlUp End If Next End Sub However, I like your method better since there is less looping. Do you know how to relate RGB to ColorIndex colors? Regards, Lee "Lee" wrote: I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
Same code, different color property...
Sub DeleteRedFontRows() Dim UserResponse As Variant On Error GoTo NoRedFonts Application.ScreenUpdating = False Application.FindFormat.Font.Color = vbRed Do Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete Loop NoRedFonts: Application.ScreenUpdating = True End Sub Note the I used the VB built in constant vbRed instead of using an RGB function call to produce the same value. -- Rick (MVP - Excel) "Lee" wrote in message ... Hmmm, that didn't work for me. Perhaps the problem is that I have a different color of Red than ColorIndex 3. This works: Sub DeleteRowsWithRedFont() Dim row As Integer For i = 2 To 81 If Range(Cells(i, 1), Cells(i, 1)).Font.Color = RGB(255, 0, 0) Then Rows(i).Delete shift:=xlUp End If Next End Sub However, I like your method better since there is less looping. Do you know how to relate RGB to ColorIndex colors? Regards, Lee "Lee" wrote: I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
Hi Lee
You can do it with this add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lee" wrote in message ... I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
That worked perfectly. I'd really like to understand the code. Would you
mind explaining why it works? Is UserResponse a special type of object? Regards, Lee "Rick Rothstein" wrote: Same code, different color property... Sub DeleteRedFontRows() Dim UserResponse As Variant On Error GoTo NoRedFonts Application.ScreenUpdating = False Application.FindFormat.Font.Color = vbRed Do Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete Loop NoRedFonts: Application.ScreenUpdating = True End Sub Note the I used the VB built in constant vbRed instead of using an RGB function call to produce the same value. -- Rick (MVP - Excel) "Lee" wrote in message ... Hmmm, that didn't work for me. Perhaps the problem is that I have a different color of Red than ColorIndex 3. This works: Sub DeleteRowsWithRedFont() Dim row As Integer For i = 2 To 81 If Range(Cells(i, 1), Cells(i, 1)).Font.Color = RGB(255, 0, 0) Then Rows(i).Delete shift:=xlUp End If Next End Sub However, I like your method better since there is less looping. Do you know how to relate RGB to ColorIndex colors? Regards, Lee "Lee" wrote: I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
Very interesting; thank you for the link.
Regards, Lee "Ron de Bruin" wrote: Hi Lee You can do it with this add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lee" wrote in message ... I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
First off, the Dim UserResponse As Variant does nothing, so you should
remove it... it was accidentally left in when I cannibalized a previous posting of mine to create my answer to you (that previous posting asked the user a question and the user's answer was placed in that variable). Now, for the code. I have the On Error statement in there because the Do..Loop is going to be run until an error occurs (which will occur when there is no more red text to find). Turning the screen updating off will speed up the code an hide all the individual deletions from the user (the error trap is used to make sure screen updating is turned back on). The FindFormat property is used in combination with the Find function and lets it do its searching using cell formats; so, I use it to establish the Font.Color as a search parameter. Next comes the Do..Loop which does the actual searching. The first argument is the text it is searching for. Since we don't care what the characters in the cell are, we just use the asterisk wildcard which tell it to search for any text no matter what that text is. Because we are looking for any text, it doesn't matter if we find it in all or part of the text, so we can ignore setting the LookAt argument (whichever it is will not matter). Since the search is being conducted only on Column A, we really don't care if the search is by column or by row, so we can ignore setting the SearchOrder argument as well. And, since the searching will continue until there is no more red text in the column, we don't care where the search starts at, so we can ignore setting the After argument. We also do not care what setting the LookIn or MatchCase currently has either (again, we are looking for any text that is red). Okay now for the SearchFormat argument... setting it to True tells the Find function to use whatever is set in the FindFormat property as part of its search criteria. All of this taken together means the Find function will only locate red text in Column A and then delete the entire row (we applied the EntireRow property to the single cell range that the Find function returns and then applied the Delete method to that entire row). -- Rick (MVP - Excel) "Lee" wrote in message ... That worked perfectly. I'd really like to understand the code. Would you mind explaining why it works? Is UserResponse a special type of object? Regards, Lee "Rick Rothstein" wrote: Same code, different color property... Sub DeleteRedFontRows() Dim UserResponse As Variant On Error GoTo NoRedFonts Application.ScreenUpdating = False Application.FindFormat.Font.Color = vbRed Do Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete Loop NoRedFonts: Application.ScreenUpdating = True End Sub Note the I used the VB built in constant vbRed instead of using an RGB function call to produce the same value. -- Rick (MVP - Excel) "Lee" wrote in message ... Hmmm, that didn't work for me. Perhaps the problem is that I have a different color of Red than ColorIndex 3. This works: Sub DeleteRowsWithRedFont() Dim row As Integer For i = 2 To 81 If Range(Cells(i, 1), Cells(i, 1)).Font.Color = RGB(255, 0, 0) Then Rows(i).Delete shift:=xlUp End If Next End Sub However, I like your method better since there is less looping. Do you know how to relate RGB to ColorIndex colors? Regards, Lee "Lee" wrote: I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row by color of font
Excellent! Thank you for the detailed explanation.
Regards, Lee "Rick Rothstein" wrote: First off, the Dim UserResponse As Variant does nothing, so you should remove it... it was accidentally left in when I cannibalized a previous posting of mine to create my answer to you (that previous posting asked the user a question and the user's answer was placed in that variable). Now, for the code. I have the On Error statement in there because the Do..Loop is going to be run until an error occurs (which will occur when there is no more red text to find). Turning the screen updating off will speed up the code an hide all the individual deletions from the user (the error trap is used to make sure screen updating is turned back on). The FindFormat property is used in combination with the Find function and lets it do its searching using cell formats; so, I use it to establish the Font.Color as a search parameter. Next comes the Do..Loop which does the actual searching. The first argument is the text it is searching for. Since we don't care what the characters in the cell are, we just use the asterisk wildcard which tell it to search for any text no matter what that text is. Because we are looking for any text, it doesn't matter if we find it in all or part of the text, so we can ignore setting the LookAt argument (whichever it is will not matter). Since the search is being conducted only on Column A, we really don't care if the search is by column or by row, so we can ignore setting the SearchOrder argument as well. And, since the searching will continue until there is no more red text in the column, we don't care where the search starts at, so we can ignore setting the After argument. We also do not care what setting the LookIn or MatchCase currently has either (again, we are looking for any text that is red). Okay now for the SearchFormat argument... setting it to True tells the Find function to use whatever is set in the FindFormat property as part of its search criteria. All of this taken together means the Find function will only locate red text in Column A and then delete the entire row (we applied the EntireRow property to the single cell range that the Find function returns and then applied the Delete method to that entire row). -- Rick (MVP - Excel) "Lee" wrote in message ... That worked perfectly. I'd really like to understand the code. Would you mind explaining why it works? Is UserResponse a special type of object? Regards, Lee "Rick Rothstein" wrote: Same code, different color property... Sub DeleteRedFontRows() Dim UserResponse As Variant On Error GoTo NoRedFonts Application.ScreenUpdating = False Application.FindFormat.Font.Color = vbRed Do Columns("A").Find("*", SearchFormat:=True).EntireRow.Delete Loop NoRedFonts: Application.ScreenUpdating = True End Sub Note the I used the VB built in constant vbRed instead of using an RGB function call to produce the same value. -- Rick (MVP - Excel) "Lee" wrote in message ... Hmmm, that didn't work for me. Perhaps the problem is that I have a different color of Red than ColorIndex 3. This works: Sub DeleteRowsWithRedFont() Dim row As Integer For i = 2 To 81 If Range(Cells(i, 1), Cells(i, 1)).Font.Color = RGB(255, 0, 0) Then Rows(i).Delete shift:=xlUp End If Next End Sub However, I like your method better since there is less looping. Do you know how to relate RGB to ColorIndex colors? Regards, Lee "Lee" wrote: I have 80 rows of data and and one of the columns contains the serial number of the row (i.e. 1 through 80). The serial number font is normally black but if the font is red, I need to find the row number to delete that row. Does anyone know how to do that? It would be nice to do it without looping to save time. Thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format for font color using font color | Excel Worksheet Functions | |||
Delete text when font is color black | Excel Programming | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
Check Font or Font color and take action | Excel Programming | |||
My fill color and font color do not work in Excel Std Edition 2003 | Excel Discussion (Misc queries) |