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

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

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

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

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