For loop to delete rows

Question

I'm using a macro that lists all of the filenames in whatever directory I choose. I'm writing code that will break apart the file name into chunks that I can use later. The list of filenames starts at cell F6 and runs down the column. Here is the code that I've written so far:

Dim ContractNum As String
Dim InvNum As String
Dim FileRng As Range
Dim FileLastRow As Long
Dim File As Range

FileLastRow = Sheet1.Range("F" & Rows.Count).End(xlUp).Row

Set FileRng = Sheet1.Range("F6:F" & FileLastRow).SpecialCells(xlCellTypeConstants, 23)

For Each File In FileRng
If File = "Invoice.zip" Or File = "Thumbs.db" Then
    File.EntireRow.Delete
End If
Next File


For Each File In FileRng
    ContractNum = Left(File, 4)
    InvNum = Mid(File, 8, 6)
    File.Offset(0, -5) = ContractNum
    File.Offset(0, -4) = InvNum
Next File

So far I've got that part working fine. The problem I'm having is that in all of the directories ill use this macro with, there are unwanted files such as "Thumbs.db" or "Invoice.zip". The code I'm having a problem with is below:

For Each File In FileRng
If File = "Invoice.zip" Or File = "Thumbs.db" Then
    File.EntireRow.Delete
End If
Next File

What I'm wanting this to do is scan through the entire list of filenames and if it encounters a filename of "Thumbs.db" or "Invoice.zip", delete the entire row. So far, this works...kinda. For example, if there are two files in my list that are named "Thumbs.db" and "Invoice.zip", I have to run the macro twice to remove both. Obviously, I would like to wipe them all out in one swoop.

Answer 1

As per my comments, change the for loop to this:

For i = filelastrow to 6 step -1
   If Sheet1.Cells(i,6) = "Invoice.zip" Or Sheet1.Cells(i,6)  = "Thumbs.db" Then
        Sheet1.row(i).Delete
   End If
Next File

The issue is that when a row gets deleted the one below becomes that row and the loop then skips it as it moves to the next. It also will then move through empty rows on the end.

By going backwards this problem is eliminated.