删除列循环VBA

本文介绍了删除列循环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.