一个 VBA For 循环中有多个范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39822889/
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
Multiple ranges in one VBA For loop?
提问by D. R
I am a beginner at VBA and am trying to highlight only empty cells in columns N and AA only. Can there be multiple ranges in one For loop to make this work instead of the following code?
我是 VBA 的初学者，并试图仅突出显示 N 列和 AA 列中的空单元格。一个 For 循环中是否可以有多个范围来完成这项工作，而不是下面的代码？
Private Sub CommandButton22_Click()
'HIGHLIGHT
Dim cell As Range
For Each cell In Range("N")
If cell.Value = vbNullString Then
cell.Interior.ColorIndex = 6
End If
Next cell
For Each cell In Range("AA")
If cell.Value = vbNullString Then
cell.Interior.ColorIndex = 6
End If
Next cell
End Sub
采纳答案by D. R
You could simply create a non-continuous range by combining the two range address like the:
您可以通过组合两个范围地址来简单地创建一个非连续范围，例如：
For Each cell In Range("N:N,AA:AA")
Next
But it is more efficient to use the Intersect method to trim the range to the used portion of the worksheet:
但是使用 Intersect 方法将范围修剪到工作表的已使用部分更有效：
For Each cell In Intersect(Range("N:N,AA:AA"), ActiveSheet.UsedRange)
If cell.Value = vbNullString Then
cell.Interior.ColorIndex = 6
End If
Next
回答by user3598756
edited to jointhe SpecialCellsapproach with the benefit of the UsedRangeas pointed out by Thomas Inzina solution
编辑加入该SpecialCells方法，受益UsedRange于 Thomas Inzina 解决方案所指出的
Use SpecialCells()method of Rangeobject and avoid looping
使用对象的SpecialCells()方法Range并避免循环
Private Sub CommandButton22_Click()
'HIGHLIGHT
Intersect(Union(Range("N:N"), Range("AA:AA")), ActiveSheet.UsedRange).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
End Sub
回答by Shiva
Yes. Use the Application.Unionmethod. That returns Areas/ A collection of the areas, or contiguous blocks of cells, within a selection.
是的。使用Application.Union方法。这将返回区域/选择中区域或连续单元格块的集合。
The following code works.
以下代码有效。
Private Sub CommandButton22_Click()
'HIGHLIGHT
Dim cell As Range
Dim target As Range
Set target = Application.Union(ActiveSheet.Range("N:N"), ActiveSheet.Range("AA:AA"))
For Each area In target.Areas
For Each cell In area
If cell.Value = vbNullString Then
cell.Interior.ColorIndex = 6
End If
Next cell
Next area
End Sub
It will color the entire column though. If you just want to color a subset, ex: from rows 10through 22in both columns, then change the union line to something like this
不过，它会为整个列着色。如果您只想为一个子集着色，例如：从行10到22两列，然后将联合线更改为这样的
Set target = Application.Union(ActiveSheet.Range("N10:N22"), ActiveSheet.Range("AA10:AA22"))
Set target = Application.Union(ActiveSheet.Range("N10:N22"), ActiveSheet.Range("AA10:AA22"))
