我通过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 SubI also recommend to declare all variables and always use Option Explicit.
更多推荐
删除列循环VBA
发布评论