对于循环删除行不起作用，需要多次运行宏

问题描述：

工作环境:Excel 2013
目标:通过过滤第2行中的内容来删除不必要的列.

Working Environment: Excel 2013
Target: Delete the unnecessary columns by filtering the content in row 2.

我的想法是，只要第2行中的内容是

My idea is that as long as the content in row 2 is either

Physical Location或
PLC Tag Name或
Test Step1/2/3/4/5/6/7，

Physical Location or
PLC Tag Name or
Test Step1/2/3/4/5/6/7,

保留这些列，否则将其删除.

keep those columns, otherwise delete it.

我的问题是我需要多次运行此宏才能删除所有不必要的列.它应该从1到40循环播放，并只保留我想要的列.我不确定为什么它不起作用. 谁能帮我?谢谢！

My problem is that I need to run this macro multiple times to delete all the unnecessary columns. It should loop from 1 to 40, and just leave the columns that I want. I am not sure why it doesn't work. Can anyone help me? Thanks!

我的代码:

Sub Reorder()
    Rows(1).Insert shift:=xlShiftDown

    For i = 1 To 40
        WY = Worksheets("Sheet4").Cells(2, i)
        Select Case WY
            Case "Physical Location"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "PLC Tag Name"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step1"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step2"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step3"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step4"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step5"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step6"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case "Test Step7"
                Worksheets("Sheet4").Cells(1, i) = 1
            Case Else
                Worksheets("Sheet4").Cells(1, i) = 0
        End Select
    Next i

    For i = 1 To 40
        If Worksheets("Sheet4").Cells(1, i) = 0 Then
            Columns(i).EntireColumn.Delete
        End If
    Next
End Sub

答

如果要删除循环中的行，则需要从底部开始.否则，实际删除的行下方的行的行数将减少1，而i将增加1(从循环中)，因此总之您会错过一行.

If you delete rows in a loop, you need to start from the bottom. Otherwise the row number of the rows below the actual deleted row decrease by one and i gets increased by one (from the loop), so in sum you miss a line.

代替

For i = 1 To 40

使用

For i = 40 To 1 Step -1
    If Worksheets("Sheet4").Cells(1, i) = 0 Then
        Columns(i).EntireColumn.Delete
    End If
Next

向后循环.

旁注:(以@ A.S.H表示感谢)
您应该使用完全合格的范围/像元/行/列，并且永远不要假定工作表.还可以使用显式选项声明所有变量.

Side note: (thx to @A.S.H)
You should use full qualified ranges/cells/rows/columns and never assume the worksheet. Also declare all your variables using option explicit.

因此

Rows(1).Insert shift:=xlShiftDown
'...
Columns(i).EntireColumn.Delete

应该是

Worksheets("Sheet4").Rows(1).Insert shift:=xlShiftDown
'...
Worksheets("Sheet4").Columns(i).EntireColumn.Delete

总而言之，我们结束于

Option Explicit 'first line in module

Public Sub Reorder()
    Dim i As Long
    Dim WY As Worksheet

    Set WY = Worksheets("Sheet4")

    WY.Rows(1).Insert shift:=xlShiftDown

    For i = 1 To 40
        Select Case WY.Cells(2, i)
            Case "Physical Location", "PLC Tag Name", "Test Step1", "Test Step2", _
                 "Test Step3", "Test Step4", "Test Step5", "Test Step6", "Test Step7"
                WY.Cells(1, i) = 1
            Case Else
                WY.Cells(1, i) = 0
        End Select
    Next i

    For i = 40 To 1 Step -1
        If WY.Cells(1, i) = 0 Then
            WY.Columns(i).EntireColumn.Delete
        End If
    Next
End Sub

或者，如果我们只使用一个循环要快得多的循环:

Or if we use only one loop which is a lot faster:

Option Explicit 'first line in module

Public Sub Reorder()
    Dim i As Long
    Dim WY As Worksheet

    Set WY = Worksheets("Sheet4")

    WY.Rows(1).Insert shift:=xlShiftDown

    For i = 40 To 1 Step -1
        Select Case WY.Cells(2, i)
            Case "Physical Location", "PLC Tag Name", "Test Step1", "Test Step2", _
                 "Test Step3", "Test Step4", "Test Step5", "Test Step6", "Test Step7"
                WY.Cells(1, i) = 1
            Case Else
                WY.Columns(i).EntireColumn.Delete
        End Select
    Next i
End Sub

对于循环删除行不起作用，需要多次运行宏

相关推荐