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

xancalagonx

Board Regular
Joined
Oct 31, 2011
Messages
57
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.

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
You cant do it with For Each. You will have to use
Code:
For cell = 100 to 1 step -1  
'do stuff' 
Next cell
for example.
 
Upvote 0
Assuming the list to be reduced is on Sheet1 and the validation list is on Sheet9, see if this macro does what you want...
Code:
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
 
Upvote 0
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.

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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top