循环遍历 VBA 中的合并单元格
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/9391092/
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
Looping through Merged cells in VBA
提问by Sarika.S
回答by brettdj
The above answers look to have you sorted.
上面的答案看起来让你排序。
If you don't know where the merged cells are then you can use the following routine to quickly detect them.
如果您不知道合并的单元格在哪里,那么您可以使用以下例程来快速检测它们。
When I built Mappit!I realised that when I developed merged cell reporting that merged cells were part of xlBlanks
当我构建Mappit 时!我意识到,当我开发合并单元格报告时,合并单元格是xlBlanks
So you can use the code to detect merged cells immediately rather than loop through each cell testing for the MergedCells
property being true.
因此,您可以使用代码立即检测合并的单元格,而不是遍历每个单元格测试MergedCells
属性是否为真。
Sub DetectMerged()
Dim rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng1 = Intersect(Cells.SpecialCells(xlFormulas), Cells.SpecialCells(xlBlanks))
Set rng2 = Intersect(Cells.SpecialCells(xlConstants), Cells.SpecialCells(xlBlanks))
On Error GoTo 0
If Not rng1 Is Nothing Then MsgBox "Merged formulae cells in " & rng1.Address(0, 0)
If Not rng2 Is Nothing Then MsgBox "Merged constant cells in " & rng2.Address(0, 0)
End Sub
回答by JMax
Here is a first stab to your issue:
这是您问题的第一个问题:
Option Explicit
Sub loopOverCells()
Dim rCell As Range
Dim i As Integer
Set rCell = [B1]
For i = 1 To 6
Debug.Print rCell.Address
Set rCell = rCell.Offset(1, 0) ' Jump 1 row down to the next cell
Next i
End Sub
回答by Jerry Beaucaire
Just a little tighter, similar idea:
稍微紧一点,类似的想法:
Option Explicit
Sub ListValues()
Dim i As Long
For i = 4 To 40 Step 6
Debug.Print Range("B" & i).Value
Next i
End Sub