循环遍历 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 15:18:07  来源:igfitidea点击:

Looping through Merged cells in VBA

excelexcel-vbavba

提问by Sarika.S

Is it possible to loop through merged cells in vba.

是否可以循环遍历vba 中的合并单元格。

  • I have 6 merged cells in the range B4:B40
  • I need the values in these 6 cells 6 iterations only.
  • 我在该范围内有 6 个合并的单元格 B4:B40
  • 我只需要这 6 个单元格中的值 6 次迭代。

回答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 MergedCellsproperty 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