VBA delete it

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
995
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
What exactly goes wrong? Is the function ment to execute on the selected cells only?
 

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
995
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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:
Code:
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 :p
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good afternoon Stephen_IV

How about something like this :

Code:
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
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

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
Top