Deleting rows which are not highlighted
 

Dear Experts

I have a huge data to work excel running into 8 thousand rows, I run a unique macro which highlights the data which are of interest. This macro changes the color of the first coloumn. Now I need a further request to document such differences which means I need to delete all the rows whose first row is not yellow. Can I get some help please.

Existing Conditions
1. Data running in 8 thousand+ or more rows.
2. Some rows has only FIRST COLUMN highlighted yellow because of the macro I run

Requirements:
Now I need to Delete rest of all the rows whose FIRST COLUMN is NOT Yellow.

Please can I get help.

Deleting rows which are not highlighted
 

Hi,

Am Mon, 4 Feb 2013 07:38:43 +0000 schrieb protocoder:

2. Some rows has only FIRST COLUMN highlighted yellow because of the
macro I run

instead of macro to highlight run macro to delete rows
If not condition to highlight then entirerow.delete


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Deleting rows which are not highlighted
 

On Sunday, February 3, 2013 11:38:43 PM UTC-8, protocoder wrote:
Dear Experts



I have a huge data to work excel running into 8 thousand rows, I run a

unique macro which highlights the data which are of interest. This macro

changes the color of the first coloumn. Now I need a further request to

document such differences which means I need to delete all the rows

whose first row is not yellow. Can I get some help please.



Existing Conditions

1. Data running in 8 thousand+ or more rows.

2. Some rows has only FIRST COLUMN highlighted yellow because of the

macro I run



Requirements:

Now I need to Delete rest of all the rows whose FIRST COLUMN is NOT

Yellow.



Please can I get help.









--

protocoder

Seems like Claus' solution make good sense.
If that doesn't work for you try this.

Option Explicit

Sub NoYellar()
Dim lRow As Long
Dim c As Range

With Sheets("sheet1")
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("A1:A" & lRow)
If c.Interior.ColorIndex < 6 Then
c.EntireRow.ClearContents
End If
Next
End With
End Sub

Regards,
Howard

Deleting rows which are not highlighted
 

Hi Howard,

Am Mon, 4 Feb 2013 00:43:27 -0800 (PST) schrieb Howard:

Sub NoYellar()
Dim lRow As Long
Dim c As Range

With Sheets("sheet1")
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("A1:A" & lRow)
If c.Interior.ColorIndex < 6 Then
c.EntireRow.ClearContents
End If
Next
End With
End Sub

to avoid looping through all cells you can filter and delete (xl2007 or
later):
Sub DeleteRows()
Dim LRow As Long
Dim LCol As Integer

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), .Cells(LRow, LCol)).AutoFilter Field:=1, _
Operator:=xlFilterNoFill
Range(.Cells(2, 1), .Cells(LRow, 1)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Deleting rows which are not highlighted
 

On Monday, February 4, 2013 1:01:42 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Mon, 4 Feb 2013 00:43:27 -0800 (PST) schrieb Howard:



Sub NoYellar()

Dim lRow As Long

Dim c As Range



With Sheets("sheet1")

lRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For Each c In .Range("A1:A" & lRow)

If c.Interior.ColorIndex < 6 Then

c.EntireRow.ClearContents

End If

Next

End With

End Sub



to avoid looping through all cells you can filter and delete (xl2007 or

later):

Sub DeleteRows()

Dim LRow As Long

Dim LCol As Integer



With ActiveSheet

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Range(.Cells(1, 1), .Cells(LRow, LCol)).AutoFilter Field:=1, _

Operator:=xlFilterNoFill

Range(.Cells(2, 1), .Cells(LRow, 1)) _

.SpecialCells(xlCellTypeVisible).EntireRow.Delete

.AutoFilterMode = False

End With

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

That's pretty slick, dealing with 8K + rows I am sure makes makes a huge difference.

I'll arcive that and give it a study, understand most of it but as a whole it's a bit above my pay grade. I was taken aback because it does not refer to interior.colorindex but the xlFilterNoFill I'm guessing takes care of that.

Thanks for the info.

Regards,
Howard