For循环删除行 [英] For loop to delete rows

问题描述

我正在使用一个宏列出我选择的任何目录中的所有文件名。我正在编写代码，将文件名分解成稍后可以使用的块。文件名列表从单元格F6开始，向下运行。以下是我迄今为止写的代码：

  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 
 
设置FileRng = Sheet1.Range（F6：F& FileLastRow）.SpecialCells（xlCellTypeConstants，23）
 
对于FileRng中的每个文件
如果File =Invoice.zip或者File =Thumbs.db然后
 File.EntireRow.Delete 
 End If 
下一个文件
 
 
对于FileRng中的每个文件
 ContractNum = Left（File，4）
 InvNum = Mid（File，8，6）
 File.Offset（0，-5）= ContractNum 
 File.Offset（0，-4）= InvNum 
下一个文件
  

这个部分工作正常。我遇到的问题是，在所有目录中都不能使用这个宏，有一些不需要的文件，如Thumbs.db或Invoice.zip。我有一个问题的代码如下：

 对于FileRng中的每个文件
如果File =发票.zip或者File =Thumbs.db然后
 File.EntireRow.Delete 
 End If 
下一个文件
  

我想要做的是扫描整个文件名列表，如果遇到Thumbs.db或Invoice.zip的文件名，则删除整个行。到目前为止，这个工作...有点儿例如，如果我的列表中有两个名为Thumbs.db和Invoice.zip的文件，我必须运行宏两次以删除这两个文件。显然，我想把它们全部擦掉。

解决方案

根据我的意见，将for循环更改为：

 对于i = filelastrow到6步-1 
如果Sheet1.Cells（i，6）=发票.zip或Sheet1.Cells（i，6）=Thumbs.db然后
 Sheet1.row（i）.Delete 
 End If 
下一个文件
  

问题是，当一行被删除时，下面的一行将成为该行，然后循环跳转到下一个行。

通过向后移动此问题将被消除。

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.

解决方案

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.

这篇关于For循环删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！