vba 如果值大于或小于，则删除整行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17814501/
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
Delete entire row if value is greater than or less than?
提问by user2611241
I am new to VBA for excel and I am stuck with a little problem. I have to delete an entire row if the value in column C is greater than 40000 or less than -40000 (these are data outliers). The list of data is thousands of lines long so I will need the action to continue until the data in column C ends. Any help would be great!
我是 excel 的 VBA 新手，我遇到了一个小问题。如果 C 列中的值大于 40000 或小于 -40000（这些是数据异常值），我必须删除整行。数据列表有数千行长，因此我需要继续执行该操作，直到 C 列中的数据结束。任何帮助都会很棒！
回答by
This code will iterate through all cells in Column Cand delete any row which value is out of your specified range
此代码将遍历列中的所有单元格C并删除值超出指定范围的任何行
Sub DeleteRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1
If Not (Range("C" & i).Value > -40000 And Range("C" & i).Value < 40000) Then
Range("C" & i).EntireRow.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
回答by Alex K.
One way;
单程;
dim rows as Range, cell as Range, value As long
set cell = Range("c1")
do until cell.value = ""
value = val(cell.value)
if (value < -40000 or value > 40000) then
if rows is Nothing then
set rows = cell.EntireRow
else
set rows = Union(cell.EntireRow, rows)
end if
end if
set cell = cell.Offset(1)
loop
if not rows is Nothing then rows.Delete
