excel中使用快捷键进行单元格的合并与拆分

首先新建一个excel表格, 选中任意的多个单元格之后, 单击view -> Macros -> record Macro, 示例:


这里需要注意一点, store macro in 字段需要设置为"Personal Macro Workbook", 这样可以让这个宏在所有的excel文档中使用. 否则只能在当前的excel中使用.

单击ok按钮之后, 使用鼠标操作合并选中的单元格, 完成之后单击view -> Macros -> stop recording. 录制宏的本质是记录用户的操作并且自动生成相应的代码, 从而可以重复调用.按下ALT + F11打开VBA编辑器, 在VBAProjects -> Modules节点下面可以找到刚才录制宏的代码, 示例:

Sub merge_cells_macro()
'
' merge_cells_macro Macro
'
' Keyboard Shortcut: Ctrl+m
'
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
End Sub

到此为止就可以使用快捷键实现基本的单元格合并功能了, 另外还可以修改上面的代码使之更为易用一些. 从该VBA代码中可以看到合并之后单元格的对其方式是常量, 而在实际的操作中往往需要合并之后的对其方式与选中的第一个单元格一致, 修改上面的代码如下:

With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

或者使用如下代码:

Sub merge_cells_macro()
Dim sa(0 To 1) As Integer
selection_align_result sa()
With Selection
.HorizontalAlignment = sa(0)
.VerticalAlignment = sa(1)
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
End Sub

Sub selection_align_result(ByRef sa() As Integer)
r = Selection.Row
rc = Selection.Rows.Count
c = Selection.Column
cc = Selection.Columns.Count

ha = xlGeneral
va = xlBottom

Dim blnFound As Boolean
For ri = r To r + rc - 1
For ci = c To c + cc - 1
Set s = Cells(ri, ci)

If Len(s.Value) > 0 Then
ha = s.HorizontalAlignment
va = s.VerticalAlignment
blnFound = True
Exit For
End If
Next
If blnFound Then
Exit For
End If
Next
sa(0) = ha
sa(1) = va
End Sub

--EOF--