vba 删除值大于特定单元格值的任何单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23224988/
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 any cells with values greater than specific cell value
提问by user3561283
I want to create a macro for the following:
我想为以下内容创建一个宏：
For each row, if there are cell values in range C3:ACP3that are >=value of ACU3, I want to replace that cell value with blank. I want to do this for every row, and each time the macro should reference the value in the ACUcolumn for that row.
对于每一行，如果范围C3:ACP3中有>=值为 的单元格值ACU3，我想用空白替换该单元格值。我想对每一行都这样做，每次宏都应该引用该ACU行列中的值。
回答by whytheq
Try this:
尝试这个：
Sub makeBlank()
Dim r As Range
Set r = Excel.ThisWorkbook.Sheets("Sheet1").Range("C3:ACP3")
Dim v As Double
v = Excel.ThisWorkbook.Sheets("Sheet1").Range("ACU3").Value
Dim c
For Each c In r
If c.Value >= v Then
c.Value = ""
End If
Next c
End Sub
EDIT
编辑
I suspect this will be quicker using arrays:
我怀疑使用数组会更快：
Sub makeBlank2()
Dim v
v = Excel.ThisWorkbook.Sheets("Sheet1").Range("ACU3").Value
Dim Arr() As Variant
Arr = Sheet1.Range("C3:ACP3")
Dim R, C As Long
For R = 1 To UBound(Arr, 1)
For C = 1 To UBound(Arr, 2)
If Arr(R, C) > v Then
Arr(R, C) = ""
End If
Next C
Next R
Sheet1.Range("C3:ACP3") = Arr
End Sub
回答by John Bustos
Try this:
尝试这个：
Sub FindDelete()
Dim ACU_Val As Double
Dim cl As Range
Dim rw As Long
For rw = 1 To Rows.Count
If Range("ACU" & rw).Value = "" Then Exit For
ACU_Val = Range("ACU" & rw).Value
For Each cl In Range("C" & rw & ":ACP" & rw)
If cl.Value >= ACU_Val Then cl.Value = ""
Next cl
Next
End Sub
回答by Seb
Simple :
简单的 ：
Dim myCell As Range
numberOfRows = 1000
For i = 0 To numberOfRows
Dim myRow As Range
Set myRow = [C3:ACP3].Offset(i, 0)
bound = Intersect([acu3].EntireColumn, myRow.EntireRow)
For Each myCell In myRow
If myCell >= bound Then myCell = ""
Next
Next
回答by PA.
you need to iterate over your desired range of cells and for each cell which contents are above the threshold value in the ACUcolumn of the same row just clear its contents.
您需要迭代所需的单元格范围，并且对于内容高于ACU同一行列中阈值的每个单元格，只需清除其内容即可。
For Each c In Range("C3:ACP3")
If c.Value >= Cells(c.Row, "ACU") Then
c.clearContents
End If
Next c
