Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
Hi Everyone,
Does anyone know a way to Delete Rows based up the row or cell color of that row? I'm working on a huge spreadsheet, about 3 to 4 thousands row, and there's a lot of rows that I need to get rid of base on the highlighted color. It's really time consuming to manually delete all those rows. Can anyone tell me if this is possible in Excel? Thank you, Neon520 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
The following macro will delete rows based on the color of a cell that you
select. I have assumed the following:- That the color refers to the Interior color (that is background color) not the font color. If this is not correct then do not use the code and advise me. That all colors are the same. If not then you might have to re-run the code for each color. The code can be modified to simply delete all rows with no color. If required this way then let me know. That the entire row is colored although the code identifies only the cells in the column in which you select the colored cell. The code will ask you to select a cell with the color to be deleted. Simply click on a single cell containing the color and click OK. You can select Cancel to abort the operation at this point. Because you are deleting rows, ensure that you backup your workbook in case the code does not do exactly what you want. Sub DeleteRows() Dim rngColorCode As Range Dim lngColorCode As Long Dim lngColumn As Long Dim i As Long On Error Resume Next Set rngColorCode = Application.InputBox _ (Prompt:="Select a cell with the interior color to be deleted", _ Title:="Color Selection", Type:=8) On Error GoTo 0 If rngColorCode Is Nothing Then MsgBox "User cancelled operation." & vbCrLf & _ "Processing terminated" Exit Sub End If lngColumn = rngColorCode.Column lngColorCode = rngColorCode.Interior.Color With ActiveSheet.UsedRange 'Must work backwards from bottom when deleteing rows. For i = .Rows.Count To 1 Step -1 If .Cells(i, lngColumn).Interior.Color = lngColorCode Then .Rows(i).Delete End If Next i End With End Sub -- Regards, OssieMac "Neon520" wrote: Hi Everyone, Does anyone know a way to Delete Rows based up the row or cell color of that row? I'm working on a huge spreadsheet, about 3 to 4 thousands row, and there's a lot of rows that I need to get rid of base on the highlighted color. It's really time consuming to manually delete all those rows. Can anyone tell me if this is possible in Excel? Thank you, Neon520 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
Replace the following text in my expanation:-
"The code can be modified to simply delete all rows with no color." with the following:- "The code can be modified to simply delete all rows EXCEPT rows with no color" -- Regards, OssieMac "OssieMac" wrote: The following macro will delete rows based on the color of a cell that you select. I have assumed the following:- That the color refers to the Interior color (that is background color) not the font color. If this is not correct then do not use the code and advise me. That all colors are the same. If not then you might have to re-run the code for each color. The code can be modified to simply delete all rows with no color. If required this way then let me know. That the entire row is colored although the code identifies only the cells in the column in which you select the colored cell. The code will ask you to select a cell with the color to be deleted. Simply click on a single cell containing the color and click OK. You can select Cancel to abort the operation at this point. Because you are deleting rows, ensure that you backup your workbook in case the code does not do exactly what you want. Sub DeleteRows() Dim rngColorCode As Range Dim lngColorCode As Long Dim lngColumn As Long Dim i As Long On Error Resume Next Set rngColorCode = Application.InputBox _ (Prompt:="Select a cell with the interior color to be deleted", _ Title:="Color Selection", Type:=8) On Error GoTo 0 If rngColorCode Is Nothing Then MsgBox "User cancelled operation." & vbCrLf & _ "Processing terminated" Exit Sub End If lngColumn = rngColorCode.Column lngColorCode = rngColorCode.Interior.Color With ActiveSheet.UsedRange 'Must work backwards from bottom when deleteing rows. For i = .Rows.Count To 1 Step -1 If .Cells(i, lngColumn).Interior.Color = lngColorCode Then .Rows(i).Delete End If Next i End With End Sub -- Regards, OssieMac "Neon520" wrote: Hi Everyone, Does anyone know a way to Delete Rows based up the row or cell color of that row? I'm working on a huge spreadsheet, about 3 to 4 thousands row, and there's a lot of rows that I need to get rid of base on the highlighted color. It's really time consuming to manually delete all those rows. Can anyone tell me if this is possible in Excel? Thank you, Neon520 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
Hi OssieMac,
Thank you SO much for replying my post. I'm thought this is impossible in Excel, but looks like your code really work. Answer to your assumption: 1. Yes, I'm referring to the "FILL" color of each cell. Is it also possible to do it base on "FONT" color? (just curious) 2. Yes and No, see my question below. If I would have two set of color identifying two different status and I want to delete both of those, I would have to just run the code twice, right? (EX: Red highlight for Terminated record, Orange for On Hold, I can just run the code twice to delete both records set, right?) 3. Would it matter if ONLY the first column is colored and the rest of the cells in such row are not. A few clarifying questions: 1. Will your code still delete the Entire row if only the first column in the row is colored, and that's the color I select when prompted? 2. What does the code react if let's say most of the cells in the row are RED, and a few are Different color, and when the code prompt, I select RED? Will the code still delete such Entire regardless the few Different Code, cause this is what I want. Thank you SO MUCH. You're awesome! Neon520 "OssieMac" wrote: The following macro will delete rows based on the color of a cell that you select. I have assumed the following:- That the color refers to the Interior color (that is background color) not the font color. If this is not correct then do not use the code and advise me. That all colors are the same. If not then you might have to re-run the code for each color. The code can be modified to simply delete all rows with no color. If required this way then let me know. That the entire row is colored although the code identifies only the cells in the column in which you select the colored cell. The code will ask you to select a cell with the color to be deleted. Simply click on a single cell containing the color and click OK. You can select Cancel to abort the operation at this point. Because you are deleting rows, ensure that you backup your workbook in case the code does not do exactly what you want. Sub DeleteRows() Dim rngColorCode As Range Dim lngColorCode As Long Dim lngColumn As Long Dim i As Long On Error Resume Next Set rngColorCode = Application.InputBox _ (Prompt:="Select a cell with the interior color to be deleted", _ Title:="Color Selection", Type:=8) On Error GoTo 0 If rngColorCode Is Nothing Then MsgBox "User cancelled operation." & vbCrLf & _ "Processing terminated" Exit Sub End If lngColumn = rngColorCode.Column lngColorCode = rngColorCode.Interior.Color With ActiveSheet.UsedRange 'Must work backwards from bottom when deleteing rows. For i = .Rows.Count To 1 Step -1 If .Cells(i, lngColumn).Interior.Color = lngColorCode Then .Rows(i).Delete End If Next i End With End Sub -- Regards, OssieMac "Neon520" wrote: Hi Everyone, Does anyone know a way to Delete Rows based up the row or cell color of that row? I'm working on a huge spreadsheet, about 3 to 4 thousands row, and there's a lot of rows that I need to get rid of base on the highlighted color. It's really time consuming to manually delete all those rows. Can anyone tell me if this is possible in Excel? Thank you, Neon520 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
Hi OssieMac,
Since your response for this post works so amazingly well. Is it possible for you to take a look at another challenge question that I have for the same project I'm working on? The title of the post is "Transpose Based on Criteria." Your response is greatly appreciated. Neon520 "OssieMac" wrote: Replace the following text in my expanation:- "The code can be modified to simply delete all rows with no color." with the following:- "The code can be modified to simply delete all rows EXCEPT rows with no color" -- Regards, OssieMac "OssieMac" wrote: The following macro will delete rows based on the color of a cell that you select. I have assumed the following:- That the color refers to the Interior color (that is background color) not the font color. If this is not correct then do not use the code and advise me. That all colors are the same. If not then you might have to re-run the code for each color. The code can be modified to simply delete all rows with no color. If required this way then let me know. That the entire row is colored although the code identifies only the cells in the column in which you select the colored cell. The code will ask you to select a cell with the color to be deleted. Simply click on a single cell containing the color and click OK. You can select Cancel to abort the operation at this point. Because you are deleting rows, ensure that you backup your workbook in case the code does not do exactly what you want. Sub DeleteRows() Dim rngColorCode As Range Dim lngColorCode As Long Dim lngColumn As Long Dim i As Long On Error Resume Next Set rngColorCode = Application.InputBox _ (Prompt:="Select a cell with the interior color to be deleted", _ Title:="Color Selection", Type:=8) On Error GoTo 0 If rngColorCode Is Nothing Then MsgBox "User cancelled operation." & vbCrLf & _ "Processing terminated" Exit Sub End If lngColumn = rngColorCode.Column lngColorCode = rngColorCode.Interior.Color With ActiveSheet.UsedRange 'Must work backwards from bottom when deleteing rows. For i = .Rows.Count To 1 Step -1 If .Cells(i, lngColumn).Interior.Color = lngColorCode Then .Rows(i).Delete End If Next i End With End Sub -- Regards, OssieMac "Neon520" wrote: Hi Everyone, Does anyone know a way to Delete Rows based up the row or cell color of that row? I'm working on a huge spreadsheet, about 3 to 4 thousands row, and there's a lot of rows that I need to get rid of base on the highlighted color. It's really time consuming to manually delete all those rows. Can anyone tell me if this is possible in Excel? Thank you, Neon520 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
I've copied your questions to this post because I think it is easier that way
to answer. EX: Red highlight for Terminated record, Orange for On Hold, I can just run the code twice to delete both records set, right? Yes. run the code for each color. 3. Would it matter if ONLY the first column is colored and the rest of the cells in such row are not. No. It does not matter which column the color is in. However, the code looks for the color ONLY IN THE SAME COLUMN where you select the color but it deletes the entire row irrespective of what other colors are in that row. Therefore if you select Yellow say in a cell column C and another row has cell in column A that is yellow and the cell in column C is not yellow then the row will not be deleted. It is possible to write code to test every cell in every row and delete the row if the code finds a match. If you are going to be running this code often then it is possibly worth the effort but if it is a one off thing then not worth the effort. A few clarifying questions: 1. Will your code still delete the Entire row if only the first column in the row is colored, and that's the color I select when prompted? Yes just so long as you select the color in a cell in the first column as per my previous answer. 2. What does the code react if let's say most of the cells in the row are RED, and a few are Different color, and when the code prompt, I select RED? Will the code still delete such Entire regardless the few Different Code, cause this is what I want. Yes just so long as it finds the selected color in the column in which you selected the color. If you really need modifications to the code to test every cell in every row then let me know. i have replied to your other question asking for more info. -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
Thank you for your clarification.
One strange thing I notice when I run the code, not quite follow your explanation. After running the code, I do a save as to compare the result. Let's say Row 2 is highlighted in RED and RED is the color I selected when prompt, according to your explanation, the Entire row 2 should be deleted and Row 3 that is in YELLOW should move up Row 2. But what happen was Row 3 move to Row 2 as well as other subsequent rows, but the ONLY the columns with data (let's say A-D) stay in YELLOW and the rest of the columns from E on are in RED. Can you explain this strange outcome? Could AutoFilter play any role in this? One more curious question if you don't mind. If the data in Excel is not uniform, is it possible to write a code to copy the data and put them back on a unified matter? Let's me explain a bit for what I mean by "Uniform" - if someone would have to Consolidate two different spreadsheet into one, and without matching the Header Row, the data from Spreadsheet 2 just got copied to the end of Spreadsheet 1, so data from SpSh 2 is not necessarily match the header row. Is it possible to write a code to match them back up? I'm talking about data from multiple spreadsheets being "jumble up" together here. I don't think that Excel is powerful enough to do this, but please correct me if I'm wrong. Best Regards, Neon520 "OssieMac" wrote: I've copied your questions to this post because I think it is easier that way to answer. EX: Red highlight for Terminated record, Orange for On Hold, I can just run the code twice to delete both records set, right? Yes. run the code for each color. 3. Would it matter if ONLY the first column is colored and the rest of the cells in such row are not. No. It does not matter which column the color is in. However, the code looks for the color ONLY IN THE SAME COLUMN where you select the color but it deletes the entire row irrespective of what other colors are in that row. Therefore if you select Yellow say in a cell column C and another row has cell in column A that is yellow and the cell in column C is not yellow then the row will not be deleted. It is possible to write code to test every cell in every row and delete the row if the code finds a match. If you are going to be running this code often then it is possibly worth the effort but if it is a one off thing then not worth the effort. A few clarifying questions: 1. Will your code still delete the Entire row if only the first column in the row is colored, and that's the color I select when prompted? Yes just so long as you select the color in a cell in the first column as per my previous answer. 2. What does the code react if let's say most of the cells in the row are RED, and a few are Different color, and when the code prompt, I select RED? Will the code still delete such Entire regardless the few Different Code, cause this is what I want. Yes just so long as it finds the selected color in the column in which you selected the color. If you really need modifications to the code to test every cell in every row then let me know. i have replied to your other question asking for more info. -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Rows Based on highlighted Color
You should not have AutoFilter applied when you run the code. However, try
the following code with a modification that tests all cells in each row for the selected color and if it finds one matching the selected color then the entire row is deleted. Sub DeleteRows() Dim rngColorCode As Range Dim lngColorCode As Long Dim i As Long Dim j As Long On Error Resume Next Set rngColorCode = Application.InputBox _ (Prompt:="Select a cell with the interior color to be deleted", _ Title:="Color Selection", Type:=8) On Error GoTo 0 If rngColorCode Is Nothing Then MsgBox "User cancelled operation." & vbCrLf & _ "Processing terminated" Exit Sub End If lngColorCode = rngColorCode.Interior.Color With ActiveSheet.UsedRange 'Must work backwards from bottom when deleting rows. For i = .Rows.Count To 1 Step -1 For j = 1 To .Columns.Count 'Test each cell in the row If .Cells(i, j).Interior.Color = lngColorCode Then .Rows(i).Delete Exit For End If Next j Next i End With End Sub -- Regards, OssieMac "Neon520" wrote: Thank you for your clarification. One strange thing I notice when I run the code, not quite follow your explanation. After running the code, I do a save as to compare the result. Let's say Row 2 is highlighted in RED and RED is the color I selected when prompt, according to your explanation, the Entire row 2 should be deleted and Row 3 that is in YELLOW should move up Row 2. But what happen was Row 3 move to Row 2 as well as other subsequent rows, but the ONLY the columns with data (let's say A-D) stay in YELLOW and the rest of the columns from E on are in RED. Can you explain this strange outcome? Could AutoFilter play any role in this? One more curious question if you don't mind. If the data in Excel is not uniform, is it possible to write a code to copy the data and put them back on a unified matter? Let's me explain a bit for what I mean by "Uniform" - if someone would have to Consolidate two different spreadsheet into one, and without matching the Header Row, the data from Spreadsheet 2 just got copied to the end of Spreadsheet 1, so data from SpSh 2 is not necessarily match the header row. Is it possible to write a code to match them back up? I'm talking about data from multiple spreadsheets being "jumble up" together here. I don't think that Excel is powerful enough to do this, but please correct me if I'm wrong. Best Regards, Neon520 "OssieMac" wrote: I've copied your questions to this post because I think it is easier that way to answer. EX: Red highlight for Terminated record, Orange for On Hold, I can just run the code twice to delete both records set, right? Yes. run the code for each color. 3. Would it matter if ONLY the first column is colored and the rest of the cells in such row are not. No. It does not matter which column the color is in. However, the code looks for the color ONLY IN THE SAME COLUMN where you select the color but it deletes the entire row irrespective of what other colors are in that row. Therefore if you select Yellow say in a cell column C and another row has cell in column A that is yellow and the cell in column C is not yellow then the row will not be deleted. It is possible to write code to test every cell in every row and delete the row if the code finds a match. If you are going to be running this code often then it is possibly worth the effort but if it is a one off thing then not worth the effort. A few clarifying questions: 1. Will your code still delete the Entire row if only the first column in the row is colored, and that's the color I select when prompted? Yes just so long as you select the color in a cell in the first column as per my previous answer. 2. What does the code react if let's say most of the cells in the row are RED, and a few are Different color, and when the code prompt, I select RED? Will the code still delete such Entire regardless the few Different Code, cause this is what I want. Yes just so long as it finds the selected color in the column in which you selected the color. If you really need modifications to the code to test every cell in every row then let me know. i have replied to your other question asking for more info. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on value | Excel Worksheet Functions | |||
Delete Rows Based on Words | Excel Discussion (Misc queries) | |||
How do I delete all rows that contain no red highlighted data? | Excel Discussion (Misc queries) | |||
Allow delete/insert cells to del/insrt highlighted rows/columns | Setting up and Configuration of Excel | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions |