使用 VBA 获取 Excel 垂直合并单元格的开始范围和结束范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2951070/
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
Get start range and end range of a vertically merged cell with Excel using VBA
提问by vbauser
I need to find out the first cell and the last cell of a vertically merged cell..
我需要找出垂直合并单元格的第一个单元格和最后一个单元格..
Let's say I merge Cells B2 down to B50.
How can I get in VBA the start cell(=B2) and the end cell(=B50)?
假设我将单元格 B2 合并到 B50。
如何在 VBA 中获得起始单元格（=B2）和结束单元格（=B50）？
回答by dendarii
Sub MergedAreaStartAndEnd()
Dim rng As Range
Dim rngStart As Range
Dim rngEnd As Range
Set rng = Range("B2")
If rng.MergeCells Then
Set rng = rng.MergeArea
Set rngStart = rng.Cells(1, 1)
Set rngEnd = rng.Cells(rng.Rows.Count, rng.Columns.Count)
MsgBox "First Cell " & rngStart.Address & vbNewLine & "Last Cell " & rngEnd.Address
Else
MsgBox "Not merged area"
End If
End Sub
回答by Mustafa Aksit
Below macro goes through all sheets in a workbook and finds merged cells, unmerge them and put original value to all merged cells.
下面的宏遍历工作簿中的所有工作表并找到合并的单元格，取消合并它们并将原始值放入所有合并的单元格。
This is frequently needed for DB applications, so I wanted to share with you.
这是 DB 应用程序经常需要的，所以我想与您分享。
Sub BirlesenHucreleriAyirDegerleriGeriYaz()
Dim Hucre As Range
Dim Aralik
Dim icerik
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Activate
MsgBox mySheet.Name & “ yap?lacak…”
For Each Hucre In mySheet.UsedRange
If Hucre.MergeCells Then
Hucre.Orientation = xlHorizontal
Aralik = Hucre.MergeArea.Address
icerik = Hucre
Hucre.MergeCells = False
Range(Aralik) = icerik
End If
Next
MsgBox mySheet.Name & " Bitti!!"
Next mySheet
End Sub
回答by ePandit
Suppose you merged B2 down to B50.
假设您将 B2 合并到 B50。
Then, start cell address will be:
然后，起始单元地址将是：
MsgBox Range("B2").MergeArea.Cells(1, 1).Address
End cell address will be:
结束单元地址将是：
With Range("B2").MergeArea
MsgBox .Cells(.Rows.Count, .Columns.Count).Address
End With
You can put address of any cell of merged area in place of B2 in above code.
您可以将合并区域的任何单元格的地址放在上面代码中的 B2 位置。
回答by AsUsual
If you intend to loop through the merged cells, try this.
如果您打算遍历合并的单元格，请尝试此操作。
Sub LoopThroughMergedArea()
Dim rng As Range, c As Range
Set rng = [F5]
For Each c In rng.MergeArea
'Your code goes here
Debug.Print c.Address'<-Sample code
Next c
End Sub
回答by Bradley Mountford
Well, assuming you know the address of one of the cells in the merged range, you could just select the offset from that range and get the row/column:
好吧，假设您知道合并范围内一个单元格的地址，您可以只选择该范围内的偏移量并获取行/列：
Sub GetMergedRows()
Range("A7").Select 'this assumes you know at least one cell in a merged range.
ActiveCell.Offset(-1, 0).Select
iStartRow = ActiveCell.Row + 1
Range("A7").Select
ActiveCell.Offset(1, 0).Select
iEndRow = ActiveCell.Row - 1
MsgBox iStartRow & ":" & iEndRow
End Sub
The code above will throw errors if the offset row cannot be selected (i.e. if the merged rows are A1 through whatever) so you will want to add error handling that tells the code if it can't offset up, the top rows must be 1 and if it can't go down, the bottom row must be 65,536. This code is also just one dimensional so you might want to add the x-axis as well.
如果无法选择偏移行，则上面的代码将抛出错误（即，如果合并的行是 A1 到任何其他行），因此您需要添加错误处理，告诉代码如果它不能向上偏移，则顶部行必须为 1如果它不能下降，那么底行必须是 65,536。此代码也只是一维的，因此您可能还想添加 x 轴。
回答by KevenDenen
If you want the cell references as strings, you can use something like this, where Location, StartCell, and EndCell are string variables.
如果您希望将单元格引用作为字符串，您可以使用类似这样的方法，其中 Location、StartCell 和 EndCell 是字符串变量。
Location = Selection.Address(False, False)
Colon = InStr(Location, ":")
If Colon <> 0 Then
StartCell = Left(Location, Colon - 1)
EndCell = Mid(Location, Colon + 1)
End If
If you want to set them as ranges, you could add this, where StartRange and EndRange are Range objects.
如果你想将它们设置为范围，你可以添加这个，其中 StartRange 和 EndRange 是 Range 对象。
set StartRange = Range(StartCell)
set EndRange = Range (EndCell)
