Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows that contain colours?
What is the easiest way to delete rows that contain colours?
If there is a way does the complete row have to be coloured or can one cell be coloured. It doesn't matter which way is used. Thankyou |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows that contain colours?
Hi,
You could use a macro. This looks at column A and if any cell is red the entire row is deleted. Right click the sheet tab, view code and paste it in. Sub stantial() Dim myrange, copyrange As Range Set myrange = Range("A1:A1000") For Each C In myrange If C.Interior.ColorIndex = 3 Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "Mally" wrote: What is the easiest way to delete rows that contain colours? If there is a way does the complete row have to be coloured or can one cell be coloured. It doesn't matter which way is used. Thankyou |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows that contain colours?
Hi Mike
Thankyou, thats great! But sorry to be a pest, whats the colour for bright yellow as thats the colour I have already used? "Mike H" wrote: Hi, You could use a macro. This looks at column A and if any cell is red the entire row is deleted. Right click the sheet tab, view code and paste it in. Sub stantial() Dim myrange, copyrange As Range Set myrange = Range("A1:A1000") For Each C In myrange If C.Interior.ColorIndex = 3 Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "Mally" wrote: What is the easiest way to delete rows that contain colours? If there is a way does the complete row have to be coloured or can one cell be coloured. It doesn't matter which way is used. Thankyou |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows that contain colours?
Hi,
6 If you aren't sure of any others you may want, record a macro of you colouring a cell and you will see the number in the recorded macro. Mike "Mally" wrote: Hi Mike Thankyou, thats great! But sorry to be a pest, whats the colour for bright yellow as thats the colour I have already used? "Mike H" wrote: Hi, You could use a macro. This looks at column A and if any cell is red the entire row is deleted. Right click the sheet tab, view code and paste it in. Sub stantial() Dim myrange, copyrange As Range Set myrange = Range("A1:A1000") For Each C In myrange If C.Interior.ColorIndex = 3 Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "Mally" wrote: What is the easiest way to delete rows that contain colours? If there is a way does the complete row have to be coloured or can one cell be coloured. It doesn't matter which way is used. Thankyou |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows that contain colours?
Thanks again
Mally "Mike H" wrote: Hi, 6 If you aren't sure of any others you may want, record a macro of you colouring a cell and you will see the number in the recorded macro. Mike "Mally" wrote: Hi Mike Thankyou, thats great! But sorry to be a pest, whats the colour for bright yellow as thats the colour I have already used? "Mike H" wrote: Hi, You could use a macro. This looks at column A and if any cell is red the entire row is deleted. Right click the sheet tab, view code and paste it in. Sub stantial() Dim myrange, copyrange As Range Set myrange = Range("A1:A1000") For Each C In myrange If C.Interior.ColorIndex = 3 Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "Mally" wrote: What is the easiest way to delete rows that contain colours? If there is a way does the complete row have to be coloured or can one cell be coloured. It doesn't matter which way is used. Thankyou |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Don't change cell colours when adding or deleting rows? | Excel Discussion (Misc queries) | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Excel 2003 font colours and cell colours | Excel Discussion (Misc queries) |