If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
remove colored rows
Hi
I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate |
#2
|
|||
|
|||
remove colored rows
Do you want to actually Delete the rows with colored cells, or do you just
want to remove the coloring from those rows but leave the data in them? To just remove color and leave the information, choose the columns and use Format | Cells and go to the [Patterns] tab (Excel 2003) and choose No Color. If you want to actually delete the rows and data, it would help to know which two columns we are talking about. Also, you mentioned "( colored cells in first and second column is equal)" -- does this mean that the row should be deleted ONLY when the cells on a row in those two columns are same not-white color? "climate" wrote: Hi I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate |
#3
|
|||
|
|||
remove colored rows
On Nov 7, 5:53 pm, climate wrote:
Hi I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate yes, you have to use macrofun 1, define a name, say "color" ---- insert-name-define, "=get.cell(63,sheet1!A1), suppose the colored cells located in column A 2, insert a column next to column A 3. enter a formula, say B1"=color",and drag down the formula 4. data filter, auto filter, customer "not equals to 0", at this time, you can get all the colored rows 5. delete the rows, 6. done. cheers. ligang |
#4
|
|||
|
|||
remove colored rows
Hummm, similar post earlier this morning...
I think this will give you what you want: Sub DeleteAllUncolored() Dim ClrRng As Range Set ClrRng = Range("A1", Range("A5000").End(xlUp)) For Each cell In ClrRng If cell.Interior.ColorIndex = 3 Then cell.EntireRow.Delete End If Next cell End Sub This assumes that cells are colored red!! This site is great to see which numbers correspond to which colors in Excel: http://www.mvps.org/dmcritchie/excel/colors.htm As always, be extremely careful when deleting date; try this macro on a sample before you execute on your actual data. Regards, Ryan-- -- RyGuy "climate" wrote: Hi I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate |
#5
|
|||
|
|||
remove colored rows
Hi thank's ryguy7272
1- My colored cells have different color(green,yellow,....). 2- your macro work's cell to cell, not for entire columns,please response to these questions. regards "ryguy7272" wrote: Hummm, similar post earlier this morning... I think this will give you what you want: Sub DeleteAllUncolored() Dim ClrRng As Range Set ClrRng = Range("A1", Range("A5000").End(xlUp)) For Each cell In ClrRng If cell.Interior.ColorIndex = 3 Then cell.EntireRow.Delete End If Next cell End Sub This assumes that cells are colored red!! This site is great to see which numbers correspond to which colors in Excel: http://www.mvps.org/dmcritchie/excel/colors.htm As always, be extremely careful when deleting date; try this macro on a sample before you execute on your actual data. Regards, Ryan-- -- RyGuy "climate" wrote: Hi I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate |
#6
|
|||
|
|||
remove colored rows
Hi ligang
I didn't understand clearly, would you please describe in detailed,or write complete code or macro. regards " wrote: On Nov 7, 5:53 pm, climate wrote: Hi I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate yes, you have to use macrofun 1, define a name, say "color" ---- insert-name-define, "=get.cell(63,sheet1!A1), suppose the colored cells located in column A 2, insert a column next to column A 3. enter a formula, say B1"=color",and drag down the formula 4. data filter, auto filter, customer "not equals to 0", at this time, you can get all the colored rows 5. delete the rows, 6. done. cheers. ligang |
#7
|
|||
|
|||
remove colored rows
Try this.
Sub DeleteAll_colored() Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Rows.Count For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "A").Interior.ColorIndex xlNone Then Rows(RowNdx).Delete End If Next RowNdx End Sub Gord Dibben MS Excel MVP On Wed, 7 Nov 2007 22:39:02 -0800, climate wrote: Hi thank's ryguy7272 1- My colored cells have different color(green,yellow,....). 2- your macro work's cell to cell, not for entire columns,please response to these questions. regards "ryguy7272" wrote: Hummm, similar post earlier this morning... I think this will give you what you want: Sub DeleteAllUncolored() Dim ClrRng As Range Set ClrRng = Range("A1", Range("A5000").End(xlUp)) For Each cell In ClrRng If cell.Interior.ColorIndex = 3 Then cell.EntireRow.Delete End If Next cell End Sub This assumes that cells are colored red!! This site is great to see which numbers correspond to which colors in Excel: http://www.mvps.org/dmcritchie/excel/colors.htm As always, be extremely careful when deleting date; try this macro on a sample before you execute on your actual data. Regards, Ryan-- -- RyGuy "climate" wrote: Hi I have 2 columns that many of it's rows has colored ( colored cells in first and second column is equal) , i want to remove colored rows. Would you please help me? regards climate |
Thread Tools | |
Display Modes | |
|
|