How can I selectivly delte rows with a macro ?

[Dan Thompson]

Hi there I have been looking for a simple way to delete rows that are
highlighted a certain color for example.

I have a worksheet where Rows 10 to 15 and 100 to 200 are interior
colorindex 4 What I would like to know is how to make vba delete all rows
that are not colored green so that only the green ones remain at the top of
the spreadsheet.

??
Dan Thompson

 

[Chip Pearson]

It isn't clear what you want. Do you want to test only cells in rows
10 to 15 and 100 to 200 and delete those rows that are not green? In
this case, use code like

Sub AAA()
Dim RowNum As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ErrH:
For RowNum = 200 To 100 Step -1
If Cells(RowNum, "A").Interior.ColorIndex <> 4 Then
' not green -- delete.
Rows(RowNum).Delete shift:=xlShiftUp
End If
Next RowNum
For RowNum = 15 To 10 Step -1
If Cells(RowNum, "A").Interior.ColorIndex <> 4 Then
' not green -- delete.
Rows(RowNum).Delete shift:=xlShiftUp
End If
Next RowNum
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

If you want to delete ALL rows that are not green from 1 to 200, use

Sub BBB()
Dim RowNum As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ErrH:
For RowNum = 200 To 1 Step -1
If Cells(RowNum, "A").Interior.ColorIndex <> 4 Then
Rows(RowNum).Delete shift:=xlShiftUp
End If
Next RowNum
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


If you have something else in mind, you can modify either of the
following to meet your needs. The key here is that you want to
deletions to go from the bottom up -- highest row number to lowest row
number. Otherwise, you'll skip some rows.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ryguy7272]

Sub DeleteAll_colored()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Interior.ColorIndex <> 4 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Please BACKUP your file before running any code...especially code that
deletes stuff. Sometimes, the results are unexpected...

HTH,
Ryan--

 

[Dan Thompson]

Thanks ryguy7272
Your code was exactly what I was looking for worked perfect

One question though why did you use a reverse loop from LastRow to 1 Step -
1 ?
Why not go from the top with a normal loop ?

 

[Chip Pearson]

The reason that you go bottom up is to prevent the code from skipping
a row. If you go from the top down, you'll miss the row following the
delete operation. For example, suppose the current value of RowNum is
5 and you delete that row. When you delete row 5, what was row 6 is
now row 5, and when RowNum is incremented from 5 to 6, you'll skip
over the current row 5, which was row 6 before 5 was deleted. Walk
through the logic step-by-step, setting row numbers after deletion and
all will become clear.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ericaamousseau]

Hello, I am trying to use this macro, but it does not seem to be working. I
am really new at VBAs, so maybe I am doign something wrong, but my gut tells
me that the reason this is not working is because the cells that are not
colorindex 4 are no fill because of a conditional format. so I have all the
columns shaded green unless column G contains *lab* then the row is formatted
to no fill. Can I not combine the VBA with the conditional format? If not -
is there a VBA that I can put in that changes row color when G has *lab*?
thanks for the help I really appriciate it!

 

[JLGWhiz]

Using conditional format to color the cell is different than using the
interior.colorindex method and requires more elaborate code to determine the
cells which are to be acted upon. The problem is that the only way to check
for the color is to check for the value of FormatConditions. This check
will show that the condition is equal to a colorindex but it does not show
if the condition is equal to true, so you can get false readings. I have
seen code that can be used, but I do not have it handy. A work around is to
use VBA to set the interior.colorindex and then Chip's code will work fine.
Or instead of checking for the color, check for the condition that sets the
color.

 

[ericaamousseau]

Okay, I figured. So how would I use a VBA to delete an entire row that
contains the word lab - the word may be inbetween other data such as
labuseonly or nonpt lab. Thanks!