EntireRow.Delete not deleting all rows

[stevec114]

Hi Everyone,

I have created a script that deletes a row based on a string found in the first column (referenced to another table with a list of strings to delete)

What is weird is that the script works but is not deleting all rows that match the criteria. For example, if there are 10 rows that match the criteria the macro will delete 6 of them and leave the other 4 rows.

I have tried changing the code and doing some debugging and I am stuck.

Sub Create_Focus()
 
Dim excel_app As Object
Dim excel_sheet As Object
Dim iMaterial As Variant
Dim LensLastRow As Variant
Dim FocusLastRow As Variant
Dim FocusSRow As Variant
Dim LensSRow As Variant
 
'Removes Lens Materials from Focus tab on Template File
Windows("ELOA_Templates.xls").Activate
Worksheets("Focus").Select
FocusLastRow = ActiveSheet.UsedRange.Rows.Count
FocusSRow = 2
Do
Windows("ELOA_Templates.xls").Activate
   iMaterial = Range("A" & FocusSRow).Value
 
'Remove Material from LENS
Windows("ELOA_MASTER.xlsm").Activate
Worksheets("LENS").Select
LensLastRow = ActiveSheet.UsedRange.Rows.Count
LensSRow = 2
Do
If Cells(LensSRow, 1) = iMaterial Then Cells(LensSRow, "A").EntireRow.Delete
'If Cells(LensSRow, 1) = iMaterial Then Range("K" & LensSRow) = 0
LensSRow = LensSRow + 1
Loop While LensSRow <= LensLastRow
FocusSRow = FocusSRow + 1
 
Loop While FocusSRow <= FocusLastRow

I originally wrote the script by using For statements ('For LensSRow =2 To LensLastRow'), either method does not make a difference. I have also tried to declare the variables and long, string, and variable to see if that made any difference.

To test the loops and the If statement I added one line of code to change the value of column k on the row to '0' when the same criteria is met. The line is commented out above, but all of rows that matched the criteria were updated correctly and the code runs fine.

I also noticed that if I add multiple lines of the same criteria to the "Focus" sheet then the amount of rows deleted increases when I run the script.

I have pretty much narrowed it down to the actual EntireRow.Delete statement.

I am officially lost, hopefully someone has a better idea of what is happening.

I would appreciate your help!

 

[stevec114]

Forgot to mention I am running Excel 2007.

 

[MrKowz]

Welcome to the forums!

You actually need to loop backwards through the rows. Go back to the FOR loop method, but use:

For LensSRow = LensLastRow To 2 Step -1

 

[stevec114]

Thank you! That worked perfectly.

It's good to know you have to loop backwards when using EntireRow.Delete.
Are there any other statements where you have to loop backward?

Steve

 

[MrKowz]

Any time you have to loop through a column and you add or delete rows (Shift:=xlDown), you need to loop backwards.