如果单元格包含 VBA/Excel 中的特定值，如何从范围中删除列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21413167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete column from range if cell contains specific value in VBA/Excel
提问by BlueFrog
I'm trying to write a bit of VBA which will check the value of a cell in a range of columns (rows M to GD), if the cell does not contain “YY” delete the column.
我正在尝试编写一些 VBA，它将检查一系列列（行 M 到 GD）中的单元格的值，如果该单元格不包含“YY”，则删除该列。
The cell to check is always in row 22
要检查的单元格始终在第 22 行
I've tried the following, but it's seriously slow.
我尝试了以下方法，但速度非常慢。
w = 186
Do
If Worksheets(“SOF”).Cells(22, w).Formula = "YY" Then
w = w - 1
Else
Worksheets(“SOF”).Cells(22, w).EntireColumn.Delete
End If
w = w - 1
Loop Until w < 13
Does anyone have any suggestions on how to speed this up or a better way to do this problem?
有没有人对如何加快速度或解决此问题的更好方法有任何建议？
Thanks
谢谢
回答by Siddharth Rout
Does anyone have any suggestions on how to speed this up or a better way to do this problem?
有没有人对如何加快速度或解决此问题的更好方法有任何建议？
Yup there is. Do notdelete the columns in a loop. Use the Unionmethod. Here is an example. I have commented the code so you will not have a problem understanding it. Still if you do then simply post back.
是的。不要删除循环中的列。使用Union方法。这是一个例子。我已经注释了代码，所以你理解它不会有问题。尽管如此，如果你这样做，那么只需回发。
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim i As Long
Dim delRange As Range
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("SOF")
With ws
'~~> Loop through relevant columns
For i = 13 To 186
'~~> Check if the value is equal to YY
If UCase(Trim(.Cells(22, i).Value)) = "YY" Then
'~~> Store the Range to delete later
If delRange Is Nothing Then
Set delRange = .Columns(i)
Else
Set delRange = Union(delRange, .Columns(i))
End If
End If
Next i
End With
'~~> Delete the relevant columns in one go
If Not delRange Is Nothing Then delRange.Delete
End Sub
This would execute in a blink of an eye but if you want, you can sandwich the code between Application.ScreenUpdating = Falseand Application.ScreenUpdating = True
这将在眨眼间执行，但如果您愿意，您可以将代码夹在Application.ScreenUpdating = False和Application.ScreenUpdating = True
