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?
 
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
 
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
kshibuAuthor Commented:
Both the soltuions did not work. So we had to manually delete the rows.
0
 
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
 
kshibuAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.