Delete Rows without any background colour via macro

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
833
I would appreciate it if someone could assist me with a macro. I need to delete any row on a spreadsheet that do not have a background colour in any cell.

i.e. row 3 may have no background colour in any cell, while row 4 may have a background colour in "4C". The macro would therefore delete row 3 only. I hope this makes scense.

Any assistance would be welcomed.

Jak
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi Jak,

This code works on the currently active sheet:
Code:
Sub DelRows()
Dim bDel As Boolean
Dim lRowFr As Long, lRowTo As Long, lRow As Long
Dim iCol As Integer, iColFr As Integer, iColTo As Integer

lRowFr = ActiveSheet.UsedRange.Row
lRowTo = lRowFr + ActiveSheet.UsedRange.Rows.Count - 1
iColFr = ActiveSheet.UsedRange.Column
iColTo = iColFr + ActiveSheet.UsedRange.Columns.Count - 1

For lRow = lRowTo To lRowFr Step -1
    bDel = True
    For iCol = iColFr To iColTo
        If Cells(lRow, iCol).Interior.ColorIndex <> xlNone Then
            bDel = False
            Exit For
        End If
    Next iCol
    If bDel = True Then Rows(lRow).Delete shift:=xlUp
Next lRow
End Sub
 
Upvote 0
Many thanks al_b_cnu

Just what I needed.

Greatly appreciated.

Jak
 
Upvote 0

Forum statistics

Threads
1,212,132
Messages
6,106,127
Members
447,996
Latest member
ANDYADAM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top