vba 宏检查某些值的单元格范围以隐藏行，如果该范围内的任何单元格包含该值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16566730/
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
Macro to check range of cells for certain value to hide rows if any cells in the range contain that value?
提问by Jamie Walker
Below is the code I have so far:
以下是我到目前为止的代码：
Sub Compare()
Sheets("SCR SYSTEM SPECS").Select
Sheets("SCR SYSTEM SPECS").Copy
Dim WS As Excel.Worksheet
Dim ColumnCount As Long
Dim I As Long
Dim Cell As Excel.Range
Set WS = ActiveSheet 'adjust as necessary
ColumnCount = 12 'adjust as necessary
With WS
For I = ColumnCount To 1 Step -1
Set Cell = .Cells(3, I)
If Cell.Value = False Then
Cell.EntireColumn.Delete
End If
Next I
End With
ActiveSheet.Shapes.Range(Array("Button 1", "Check Box 1", "Check Box 2", _
"Check Box 3", "Check Box 4", "Check Box 5", "Check Box 6", "Check Box 7", _
"Check Box 8", "Check Box 9", "Check Box 10", "Check Box 11")).Select
Selection.Delete
End Sub
What I need is a macro to go below all of this to loop through range B4:L4 and check each cell to see whether it ends with an X or not. This row will contain any combination of the following numbers/text: 1300, 2000, 2000X, 2500, 2500X, 3000, 3000X, 4500, 6000, 7000, 9000. I need to say if none of these cells end in X then hide or delete certain rows. I've been trying to use If Not Like "*X" unsuccessfully. Below is the code I have tried so far which has been unsuccessful. Any help is much appreciated.
我需要的是一个宏来在所有这些之下循环遍历范围 B4:L4 并检查每个单元格以查看它是否以 X 结尾。此行将包含以下数字/文本的任意组合：1300、2000、2000X、2500、2500X、3000、3000X、4500、6000、7000、9000。我需要说如果这些单元格中没有一个以 X 结尾，则隐藏或隐藏删除某些行。我一直在尝试使用 If Not Like "*X" 失败。以下是我迄今为止尝试过但未成功的代码。任何帮助深表感谢。
Dim MyCell, Rng As Range
Set Rng = Sheets("SCR SYSTEM SPECS").Range("B4:L4")
For Each MyCell In Rng
If Not MyCell Like "*X" Then '''''will only do something if the cell is not blank
Rows("4:18").Select
Selection.EntireRow.Hidden = True
'Else '''''if cell is equal to blank
'Rows("4:18").Select
'Selection.EntireRow.Hidden = False
End If
Next
This is the altered code I am trying to use to hide rows that contain all 0's but it is hiding the rows whether they contain all 0's or if they contain 2 columns with a 0 then a column with a 4. Please advise.
这是我试图用来隐藏包含全 0 的行的更改代码，但它隐藏了行是否包含全 0 或如果它们包含 2 列 0 然后一列 4。请告知。
Dim MyCell2, Rng2 As Range
Set Rng2 = Sheets("SCR SYSTEM SPECS").Range("B36:L36")
For Each MyCell2 In Rng2
If Right(Trim(MyCell2.Value), 1) = "0" Then
Range("36:36").Select
Selection.EntireRow.Hidden = True
End If
Next
回答by David Zemens
The LIKEfunction is tricky, and probably not what you want since it's fuzzy, anyways.
这个LIKE函数很棘手，可能不是你想要的，因为它很模糊，反正。
Use the RIGHTfunction:
使用RIGHT函数：
Sub TestThis()
Dim MyCell, Rng As Range
Set Rng = Sheets("SCR SYSTEM SPECS").Range("B4:L4")
For Each MyCell In Rng
If Right(Trim(MyCell.Value),1) = "X" Then '''''will only do something if the cell is not blank
Rows("4:18").EntireRow.Hidden = True
'Else '''''if cell is equal to blank
'Rows("4:18").EntireRow.Hidden = False
End If
Next
End Sub
I also revise the above code to avoid Selection, which is unnecessary about 99% of the time.
我还修改了上面的代码以避免Selection，这在 99% 的情况下是不必要的。
Because you're looking for an "If NONE of these cells contain..." I would suggest the following additional revision, to Exitthe loop once the condition is met.
因为您正在寻找“如果这些单元格中没有一个包含...”，我建议在Exit满足条件后对循环进行以下附加修订。
Sub TestThis()
Dim MyCell, Rng As Range
Set Rng = Sheets("SCR SYSTEM SPECS").Range("B4:L4")
For Each MyCell In Rng
If Right(Trim(MyCell.Value),1) = "X" Then '''''will only do something if the cell is not blank
Rows("4:18").EntireRow.Hidden = True
Exit For '## Exit the loop once the condition is met ##'
'Else '''''if cell is equal to blank
'Rows("4:18").EntireRow.Hidden = False
End If
Next
End Sub
