How do I delete the row when it has one cell highlighted?

**Ashleyyy** · 01-09-2012, 06:17 AM

Hi,
My data has about 5000 rows and 30 columns.

Based on a criteria, I have used conditional formatting in excel to highlight some cells. I would like to delete all the rows that have a cell highlighted (please refer to attached excel file for example). How may I do this?

Thank you.

Regards,
Ashley

**OnErrorGoto0** · 01-09-2012, 06:21 AM

Hi,
Which version of Excel are you actually using? Your profile says 2003 but that workbook is in the newer format and, if you have 2010, it is reasonably easy to do.

**Ashleyyy** · 01-09-2012, 06:33 AM

Hi,
I'm using my friend's computer and he has excel 2010. My excel is 2003. You can guide me on how to do this using excel 2010 as I am using his com now.

Thank you.

Regards,
Ashley

**Bmoe** · 01-09-2012, 06:38 AM

Ashley,

You could make another formula row that gives you a true false row that would make it easier to check if the row needs to be deleted.

Or you could check every cell in the range.

Sub deleteRow()
Dim myRange As Range
Dim myCell As Range

Set myRange = Range("A3:D8")


For Each myCell In myRange
If myCell.Value = "hh" Or myCell.Value = "pp" Or myCell.Value = "ss" Then
 myCell.EntireRow.Delete
End If


Next myCell
End Sub

I was having trouble with extracting the conditional state from the cell object too, I just replicated the conditions in the if block for the code.

Either way Range().entireRow.delete command will do what you want.

**OnErrorGoto0** · 01-09-2012, 06:43 AM

For 2010, you can use the DisplayFormat like this

Sub delHighlightedCells()
   Dim rgToDelete As Range
   Dim i As Long, j As Long
   
   For i = ActiveSheet.UsedRange.Row To ActiveSheet.UsedRange.Rows.Count
      For j = ActiveSheet.UsedRange.Column To ActiveSheet.UsedRange.Columns.Count
         If Cells(i, j).DisplayFormat.Interior.Color = 255 Then
            If rgToDelete Is Nothing Then
               Set rgToDelete = Rows(i)
            Else
               Set rgToDelete = Union(rgToDelete, Rows(i))
            End If
            Exit For
         End If
      Next j
   Next i
   If Not rgToDelete Is Nothing Then rgToDelete.Delete
End Sub

**Ashleyyy** · 01-09-2012, 07:05 AM

Hi all,
I should have mentioned this earlier, I'm a VBA noob.

Bmoe,
Am I right to say that your code doesn't check for highlights, it just detects a certain value in cells and deletes a row that has a cell with that value?

OnErrorGoto0,
I ran your code verbatim and it works perfectly.

Thanks to both of you!!

Regards,
Ashley

**OnErrorGoto0** · 01-09-2012, 07:23 AM

255 is the RGB value for Red.

**Ashleyyy** · 01-10-2012, 10:04 AM

Hi OnErrorGoto0,
Thank you!

Regards,
Ashley

**Ashleyyy** · 02-11-2012, 07:25 AM

Hi,
I just tried to run this code again:

Sub delHighlightedCells()
   Dim rgToDelete As Range
   Dim i As Long, j As Long
   
   For i = ActiveSheet.UsedRange.Row To ActiveSheet.UsedRange.Rows.Count
      For j = ActiveSheet.UsedRange.Column To ActiveSheet.UsedRange.Columns.Count
         If Cells(i, j).DisplayFormat.Interior.Color = 255 Then
            If rgToDelete Is Nothing Then
               Set rgToDelete = Rows(i)
            Else
               Set rgToDelete = Union(rgToDelete, Rows(i))
            End If
            Exit For
         End If
      Next j
   Next i
   If Not rgToDelete Is Nothing Then rgToDelete.Delete
End Sub

and excel throws up the error

Runtime error 438, object does not support this property of method.

When I hit debug, excel highlights

If Cells(i, j).DisplayFormat.Interior.Color = 255 Then

Help?

Thanks

**OnErrorGoto0** · 02-11-2012, 08:23 AM

Are you running it in 2010?

**Ashleyyy** · 02-11-2012, 10:36 AM

yes, i am.

**OnErrorGoto0** · 02-11-2012, 11:09 AM

Then it should work. (it won't work in any other version) What format is the file saved in?

**jolivanes** · 02-11-2012, 12:50 PM

You could try to see what the actual color is by selecting the colored cell and running this

Sub ShowColour()
Dim RGBColour As String, R As Integer, G As Integer, B As Integer
RGBColour = Right("000000" & Hex(ActiveCell.Interior.Color), 6)
R = WorksheetFunction.Hex2Dec(Right(RGBColour, 2))
G = WorksheetFunction.Hex2Dec(Mid(RGBColour, 3, 2))
B = WorksheetFunction.Hex2Dec(Left(RGBColour, 2))
MsgBox "RGB" & vbTab & R & ", " & G & ", " & B & vbCrLf & "Index" & vbTab & ActiveCell.Interior.ColorIndex
End Sub

If you use a color from the color palette you can change the

If Cells(i, j).DisplayFormat.Interior.Color = 255 Then

to

If Cells(i, j).DisplayFormat.Interior.ColorIndex = ?? Then  '<---- substitude question marks with number from Message Box

Might work

Regards
John

Thread: How do I delete the row when it has one cell highlighted?

Thread Tools

Display

How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Re: How do I delete the row when it has one cell highlighted?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions