VBA delete it

[Stephen_IV]

I am trying to delete the entire row if the colorindex = 39 the problem is that this is not working properly. I need it to operate on 15000 cells.

Thanks in advance

Sub deleteit()
For Each cell In Selection
If cell.Interior.ColorIndex = 39 Then cell.EntireRow.Delete
Next
End Sub

 

[Harvey]

What exactly goes wrong? Is the function ment to execute on the selected cells only?

 

[Stephen_IV]

it is suppose to work on the selection but what is happening is that it only deletes the first few and thats it? I do not know what would cause this to happen. If I select 200 cells it will not delete all of the colorindex 39 cells. The cells are not conditional formatted.

 

[Boller]

To delete like this with a loop, you have to go from bottom to top instead of from top to bottom.

But then there might be better ways to do it.
Provide more description of what you want to do.
Are you selecting cells in one column only (if so what column)?
How many cells are you normally selecting?

 

[Harvey]

what boller said is true. If you are going from lets say row 5 to 10, and row 6 and row 7 are to be deleted, your row 6 comes first and is deleted. Now row 7 became row 6 and will never be even looked at by your code.
Try this altered version:

Sub deleteit()

Dim minrow As Integer
Dim maxrow As Integer
Dim mincol As Integer
Dim maxcol As Integer
Dim rowcount As Integer
Dim colcount As Integer

minrow = Selection.Row
maxrow = Selection.Rows.Count + minrow - 1
mincol = Selection.Column
maxcol = Selection.Columns.Count + mincol - 1

For rowcount = maxrow To minrow Step -1
    For colcount = maxcol To mincol Step -1
        If Cells(rowcount, columncount).Interior.ColorIndex = 39 Then cell.EntireRow.Delete
    Next colcount
Next rowcount

End Sub

I havent tested it, but this is the general idea. If it doesn't work I hope you can figure it out yourself

 

[DominicB]

Good afternoon Stephen_IV

How about something like this :

Sub deleteit()
For n = Selection.Count To 1 Step -1
If Cells(ActiveCell + n, ActiveCell.Column).Interior.ColorIndex = 6 Then
Cells(ActiveCell + n, ActiveCell.Column).EntireRow.Delete
End If
Next n
End Sub

HTH

DominicB