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
|