Using For Next to delete unwanted rows, but only deletes half at a time???

[xancalagonx]

So what I am trying to do is delete unwanted rows in one Sheet if they do not meet the validation values from 2nd sheet.

I have a tag list with tags that are associated to a system. Some systems are not part of the project so I need to purge these from the excel list before I can run a status report

So sheet1 has the complete list of all tags in all systems.
And sheet2 has a list of only the valid systems for current project.

My thought was to run a double For-Next loop where I go through all tags in Sheet1, compare the system to all systems in sheet2, and if I get a hit (ie the tag belongs to a valid system) it changes a boolean value to True.

At end of the For-Next loop it checks the boolean value and if it is False it should delete the entire row containing the tag.

However, when I run the code against a sheet with only invalid systems on it then will only delete half the rows. Then I run the code again and it deletes half of the remaining half. I run it again and it deletes half of whats left... and so on.

Sub Delete Rows()

Application.ScreenUpdating = False


Sheet1.Activate
    pProjSubRange = Range("A:A").SpecialCells(xlCellTypeConstants) [COLOR=#00ff00]'selects the range of valid systems from sheet1[/COLOR]
Sheet9.Activate
    'ccmsSubRange = Range("B:B").SpecialCells(xlCellTypeConstants) [COLOR=#00ff00]'selects the range/column containing the system numbers to be validated in the main sheet[/COLOR]


Dim pProjSubVerified As Boolean [COLOR=#00ff00]'used to trigger if row should be deleted or not[/COLOR]


For Each ccmsDumpSub In Range("B:B").SpecialCells(xlCellTypeConstants)
    pProjSubVerified = False [COLOR=#00ff00]'I reset the value to false at start of each for-next loop.[/COLOR]
    For Each pProjSub In pProjSubRange [COLOR=#00ff00]'then I run this For-Next loop inside the other one to compare the system against all the valid systems[/COLOR]

        If ccmsDumpSub = "SubSystem" Then GoTo Skip[COLOR=#00ff00] 'I use this line to skip the heading to avoid it deleting the heading due to not finding it in the valid subsystem list.[/COLOR]

        If ccmsDumpSub = pProjSub Then pProjSubVerified = True [COLOR=#00ff00]'sets boolean to True if it finds a matching system in the valid system list.[/COLOR]
    Next pProjSub
    If pProjSubVerified = False Then ccmsDumpSub.EntireRow.Delete[COLOR=#00ff00] 'if it found no matches it deletes the row in the main sheet[/COLOR]
Skip:
Next ccmsDumpSub

I felt the logic here was pretty solid... and it works when I run the code... except it only deletes half the rows that it should... and I have to re-run the code multiple times which, to me, is not making sense.

Perhaps some of you more experienced VBA users will see a glaring mistake in here and could give me a heads up on what is causing it or why it is happening (deleting only half the matches that is).

Thanks

 

[steve the fish]

Usually when using looping through cells and deleting rows you need to work from the bottom to the top. When you delete a row the cell below the activecell at deletion becomes the activecell. Then you move down a cell. Thus missing testing the cell.

 

[xancalagonx]

That makes sense

So how do I loop it from bottom and up ?

 

[steve the fish]

You cant do it with For Each. You will have to use

For cell = 100 to 1 step -1  
'do stuff' 
Next cell

for example.

 

[Rick Rothstein]

Assuming the list to be reduced is on Sheet1 and the validation list is on Sheet9, see if this macro does what you want...

Sub ReduceList()
  Dim R As Long, Valid As String
  Valid = Chr(1) & Join(Application.Transpose(Sheets("Sheet9").Range("B1", _
          Sheets("Sheet9").Cells(Rows.Count, "B").End(xlUp))), Chr(1)) & Chr(1)
  For R = 2 To Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    If InStr(Valid, Chr(1) & Sheets("Sheet1").Cells(R, "A") & Chr(1)) = 0 Then
      Sheets("Sheet1").Cells(R, "A").Value = False
    End If
  Next
  Sheets("Sheet1").Columns("A").SpecialCells(xlConstants, xlLogical).EntireRow.Delete
End Sub

 

[xancalagonx]

Rick, I got a 'subscript out of range' error when I used your code snippet.

Anyways, the runtime with my own code was taking a year so I rewrote it. I think it was taking forever because it added all the blank cells into the array as well, then comparing one cell in sheet9 (main sheet) against all cells, including blank ones, in the validation array (and in Excel 2010 that's what... 1 million+ values to check against for each of the 8500~ tags in the main sheet..)

Using .SpecialCells(xlCellTypeConstants) I think I killed the runtime issue.

However I still need to run the loop from bottom-up since I still have the issue where it only deletes half the rows.

I don't understand how to add Steve's suggestion into the For Next loop as it is in my code.

Dim arrProj As Variant
Dim aCCMS As Variant
Dim aPro As Variant
Dim bVer As Boolean


Sheet1.Activate
    arrProj = Range("A:A").SpecialCells(xlCellTypeConstants).Value 'added all the validation values into an array

Sheet9.Activate


For Each aCCMS In Range("B:B").SpecialCells(xlCellTypeConstants)
    bVer = False
    For Each aPro In arrProj
        If aCCMS = aPro Then bVer = True
        If aCCMS = aPro Then GoTo skip
    Next aPro
If bVer = False Then aCCMS.EntireRow.Delete
skip:
Next aCCMS

 

[Rick Rothstein]

Rick, I got a 'subscript out of range' error when I used your code snippet.

Which line of my code did that error occur on?

Also, about how many rows of data are there on Sheet1?

 

[xancalagonx]

Rick,
It happened on the first one "Valid ="

The list has 8500 rows of data, and approximately 5500-6000 of those rows are not relevant to what I am reporting (ie. why I am trying to have them removed).

I found a way to do it though, as per the code above, and with a bit of google-magic I figured out how to delete the rows in reverse order... or bottom up as it were.

Thanks though

If I understood your code though, you basically stitched them all together then searched for matches within the string to identify and eliminate the rows?

I certainly learned a lot trying to get my macro to work, but still have lots to learn to be more efficient with VBA coding.