Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 73
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Delete empty rows in excel in b/w rows with values Dennis Excel Worksheet Functions 3 August 28th 07 04:15 PM
Don't change cell colours when adding or deleting rows? Martin ©¿©¬ @nohere.net Excel Discussion (Misc queries) 1 April 16th 07 03:32 PM
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows Scott Excel Worksheet Functions 0 December 13th 06 02:25 AM
How to delete rows when List toolbar's "delete" isnt highlighted? Linda Excel Worksheet Functions 1 May 26th 05 08:39 PM
Excel 2003 font colours and cell colours bretta Excel Discussion (Misc queries) 1 April 17th 05 03:45 AM


All times are GMT +1. The time now is 04:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"