j = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
For i = j To 1 Step -1
If WS.Cells(1, i).Value = "" Or WS.Cells(1, i).Value = "--" Then
WS.Columns(i).EntireColumn.Delete
End If
Next i
目前,我正在执行从最后一列到第一列的反向 for 循环,有条件地删除该列。我想知道是否有更智能、更快捷的方法来做到这一点。
答案1
如果您想更快地删除列,请一次删除所有列,而不是一次删除一个列。
Sub DeleteEmptyColumns()
Dim columnsToDelete As Range
lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
For i = lastColumn To 1 Step -1
If WorksheetFunction.CountA(Columns(i)) = 0 Then
If columnsToDelete Is Nothing Then Set columnsToDelete = Columns(i) Else Set columnsToDelete = Union(columnsToDelete, Columns(i))
End If
Next i
columnsToDelete.Delete
End Sub
答案2
以下是根据文章内容提出的另一种表述 如何在 Microsoft Excel 中通过 VBA 删除空列,但我不知道它是否会更快。
j = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
For i = j To 1 Step -1
If WorksheetFunction.CountA(Columns(j)) = 0 Then
Columns(j).Delete
End If
Next i