I have several coloured rows. I want to delete them all at once.

I have several coloured rows in Excel 2007. I want to delete them all at once.
LVL 1
kshibuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gašper KamenšekExcel MVPCommented:
Hi,

one way of doing that is by filter. Where you can filter by color and therefor get only the colored rows. Then you just select them and delete them.
For deletion you can use Ctrl+-(minus) and if you only selected cells choose shift cells up. You will not get empty rows that way....

Happy holidays and good luck...
0
Gašper KamenšekExcel MVPCommented:
Another way to select only colored cells is by macro

Sub SelectByColor()
'
' Selects by RGB value
'

'
    Dim rCell As Range
    Dim lColor As Long
    Dim rColored As Range
    Dim Selectio As Range

    lColor = RGB(255, 255, 255)
   

    Set rColored = Nothing
    For Each rCell In Selection
        If rCell.Interior.Color = lColor Then
            If rColored Is Nothing Then
                Set rColored = rCell
            Else
                Set rColored = Union(rColored, rCell)
            End If
        End If
    Next
    If rColored Is Nothing Then
        MsgBox "There is no such color used in this WorkBook"
    Else
        rColored.Select
    End If
    Set rCell = Nothing
    Set rColored = Nothing
End Sub

If you know the RGB value of the color, you can select the whole sheet, than run this macro, but first change the color value in the macro...

Good Luck

0
kshibuAuthor Commented:
Both the soltuions did not work. So we had to manually delete the rows.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Gašper KamenšekExcel MVPCommented:
Hi,

Than something must have gone wrong since both solutions are implemented in my working environment and are used daily and haven't yet failed...

Did you adjust the RGB value in a macro and previously selected the whole sheet?

Have a nice day
0
kshibuAuthor Commented:
Will this solution work on rows.
0
Gašper KamenšekExcel MVPCommented:
Hi,

If you are talking about the VBA code, it will only work If the entire row is of that same color.

But with filter (the first solution), where you manually delete the rows, it will work either way.

If you want, you can attach a sample file, and I will write a macro for you, so that will work in your situation and delete the rows by itself, since the sample macro I gave you only selects those cells that have that color...

Have a nice day and good luck...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kshibuAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.