Deleting rows which are not highlighted

[protocoder]

Dear Experts

I have a huge data to work excel running into 8 thousand rows, I run
unique macro which highlights the data which are of interest. This macr
changes the color of the first coloumn. Now I need a further request t
document such differences which means I need to delete all the row
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 th
macro I run

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

Please can I get help

 

[Claus Busch]

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

 

[Howard]

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.

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

 

[Claus Busch]

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

 

[Howard]

Hi Howard,



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












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