删除列循环VBA [英] Delete Column Loop VBA

问题描述

我通过While循环遍历Sheet，所以当我在第一行找到字符串Out时，我删除该行。
问题是，当我删除该列时，下一个进入删除的位置，因此循环将通过，不会删除它。

你能帮助我有一个

谢谢

  Sub DeleteCol（）
 
 xx = 37 
做工作表（数据）。细胞（1，xx） 
 
 Agrup = Worksheets（Data）。单元格（1，xx）
 Rubri =工作表（Data）。单元格（2，xx）
如果Agrup =Out然后
'Worksheets（Data）。列（xx）.Clear 
工作表（Data）。列（xx）.Delete Shift：= xlShiftToLeft 
 End If 
 xx = xx + 1 
循环
 
 End Sub 
  

解决方案

删除行或列时，总是需要向后循环。

这是因为删除总是影响之后的行/列的位置当前行/列而不是之前。因此，向后循环不会影响我们的循环，因为未经处理的行/列在之前 之前不受影响的行/列。

  Option Explicit'非常第一行，以确保所有变量都声明为
 
 Public Sub DeleteColumns（）
 Dim ws As Worksheet 
设置ws =工作表（数据）'定义工作表
 
 Dim Argup As Range，Rubri As Range 
 Dim iCol As Long，lCol As Long'iteration column，last column 
 Const fCol = 37'第一列
 
使用ws'<  - 使用With语句
 lCol = .Cells（1，.Columns.Count）.End（xlToLeft）。列'查找最后使用的列
 
对于iCol = lCol到fCol步骤-1'从最后一列向后循环到列37 
 Agrup = .Cells（1，iCol）
 Rubri = .Cells（2，iCol）
 
如果Argup =Out然后
 .Columns（iCol）.Delete Shift：= xlShift ToLeft 
结束如果
下一个iCol 
结束
 
 End Sub 
  

我还建议声明所有变量，并始终使用 Option Explicit 。

I loop through the Sheet via While, so when I find the string "Out" in the first row I delete the row. The problem is that when I delete that column the next one goes to the deleted position and thus the loop will pass and wont delete it.

Can you help me?

Thanks

Sub DeleteCol()

xx = 37
Do While Worksheets("Data").Cells(1, xx) <> ""

Agrup = Worksheets("Data").Cells(1, xx)
Rubri = Worksheets("Data").Cells(2, xx)
If Agrup = "Out" Then
    'Worksheets("Data").Columns(xx).Clear
    Worksheets("Data").Columns(xx).Delete Shift:=xlShiftToLeft
End If
xx = xx + 1
Loop

End Sub

解决方案

When deleting rows or columns you always need to loop backwards. Otherwise the current row/column position changes during delete.

This is because deleting always affects the position of rows/columns after the current row/column but not before. Therefore looping backwards does not affect our loop because un-processed rows/columns are before current row/column which are not affected by deleting.

Option Explicit 'very first line to ensure all variable are declared

Public Sub DeleteColumns()
    Dim ws As Worksheet
    Set ws = Worksheets("Data") 'define worksheet

    Dim Argup As Range, Rubri As Range
    Dim iCol As Long, lCol As Long 'iteration column, last column
    Const fCol = 37 'first column

    With ws '<-- use With statement
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column 'find last used column

        For iCol = lCol To fCol Step -1 'loop from last column backwards to column 37
            Agrup = .Cells(1, iCol)
            Rubri = .Cells(2, iCol)

            If Argup = "Out" Then
                .Columns(iCol).Delete Shift:=xlShiftToLeft
            End If
        Next iCol
    End With

End Sub

I also recommend to declare all variables and always use Option Explicit.

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