入门必学 VBA批量合并拆分单元格

本技巧的目的:掌握单元格合并,拆分,求和的方法。

  • 用代码合并单元格后的效果

  • 用代码拆分单元格后的效果

  • 如果需要在拆分后将原有的文字全部保留,在单元格中实现是很麻烦的一件事,但使用VBA代码形式很简单。

代码助手实现代码快速输入

  • 代码助手下载地址 http://excel880.com/blog/archives/11297

  • 只要在代码区输入中文:合并+空格 就会弹出相应的代码 以供选择

  • 使用代码助手还能够随时收录自己需要的代码:

如何判断单元格区域是否存在合并单元格

  • 代码如下:

  • 注释:

  • 使用 Range.MergeCells 属性 检查 单元格中是否包含合并单元格

  • 可以使用立即窗口来测试一下三个不同的结果:

  • 当有合并单元格时,返回的结果为 True

  • 当没有合并单元格时,返回的结果为 False

  • 当混合二个不同类型的单元格时,返回的结果为 Null

  • 代码的书写就是根据三种不同的返回值来书写

  • Selection.MergeCells = True  '有合并单元格

  • IsNull(Selection.MergeCells)  '检查变量值是否为 Null ,即是否混合二种格式的单元格

  • Else '最后为无合并的单元格

  • IsNull 函数的 示例 1:

  • Dim 值, 检查结果

  • 检查结果 = IsNull(值)

  • '因为值是空值,则:检查结果 = False。

  • 示例 2:

  • 值 = ""

  • 检查结果 = IsNull(值)

  • '因为值设置为空值,则:检查结果= False。

  • 示例 3:

  • 值 = Null

  • 检查结果 = IsNull(值)

  • '值设置为:Null 则:检查结果 = True。

  • 将上面的代码修改一下就变成通用的函数了,这样可以方便随时调用

  • 注释:

  • 一般子函数, 只要不需要外部调用, 无论是否有返回值, 都可以使用 function 语句

  • 括号内的 r 为外部传入的参数,外部传入时可以不使用 r 这个变量, 可以任意名称, 只要类型与当前子函数相符即可

  • 参数传入后, 无论外部是什么名称, 当前过程会自动转换为 r 。参数 r 可以直接在当前过程中使用,不需要声明

  • 如果要返回结果, 函数名称需要写上等于哪个值, 即: 判断合并单元格 = s

  • 子函数写好后,直接调用就可以出来结果:

合并单元格时能够连接每个单元格的文本的方法

  • 合并前:

  • 合并单元格的代码:

  • 注释:

  • 先将原单元格的内容全部用连接的方式记录下来

  • 合并单元格:rng.Merge

  • s = s & "," & r  '第1次连接时 s 为空值, 结果变成 ,453, 后面的连接是正常的: ,453,189.... 如上图

  • rng = Mid(s, 2) '使用 Mid 去掉第1个 , 逗号

  • WrapText 属性:所有单元格中的文本都自动换行,返回 True;都不自动换行,返回 False;有些单元格中的文本自动换行,有些不自动换行,返回 Null

  • 同样的,修改成子函数,方便后续调用:

  • 执行前:

  • 执行后:

  • 注释:

  • 把三个地址放入数组:"A1:A4", "C3:C5", "E6:E8"

  • 调用子函数--合并单元格所有内容, 执行合并单元格的操作

  • Function 合并单元格所有内容(rng As Range),括号内的 rng 是外部传入的参数

  • 子函数的优点:主函数不需要每次都写一大段代码,可以通过传参数的方法在子函数中完成,子函数可以被多个过程调用。变量也可以相对减少很多。

  • rng = "'" & Mid(s, 2)

  • '加上"'"代表是以文本写入,因为有数字,可以防止写入到单元格中出错

合并内容相同的连续单元格

  • 合并前的格式:

  • 合并后的格式及代码:

  • 注释:

  • 加速语句,有关必须有开, 都是成对的。Application.DisplayAlerts = False  Application.DisplayAlerts = True

  • 位置语句开始后面,所有需要使用该位置的地址前面都要加上 .

  • ★★★ 注意:合并单元格,删除单元格,为了防止单元格的位置混乱,都必须是从下到上的循环, step 是 -1

  • .Range(.Cells(i - 1, "A"), .Cells(i, "A")).Merge '二个单元格合并,range(最上面单元格,最下面单元格)

拆分单元格时仍然保留每个单元格中的内容

  • 拆分前面已合并的单元格,代码如下:

  • 注释:

  • 取消合并单元格,从上到下即可

  • 先记录原值,再写入的过程

  • 循环行号需要变化:i = i + rng.Rows.Count - 1 即:循环行号(本行) + 包含本行内的单元格的行数 - 1 本行

  • MergeArea 属性:返回单元格对象。如果在合并区域,返回单元格的合并区域。如果不在合并区域内,返回该单元格

  • n = .Range("A1048576").End(xlUp).Row + _

  • .Range("A1048576").End(xlUp).MergeArea.Rows.Count - 1

  • '为了防止最后行为合并的单元格, 出现计算不准确情况。使用这个公式计算最大行: 最大行 + 最大行的合并区域的行数 - 1

合并单元格对应同行区域求和

  • 代码:

  • 注释:

  • Application.ThisCell:返回当前单元格, 如图为:C2 它的 MergeArea 合并区域:C2:C4

  • r.Resize(e.Rows.Count) '传入的参数 r 为: A2,它扩展 (C2:C4 的行数:3),即:A2:A4

  • Application.WorksheetFunction.Sum(e)  '对该区域调用工作表函数执行求和

  • 在选定区域中写入公式的代码:

  • 选定区域为: B2:B9

  • 注释:

  • 打断点逐步调试看结果, 方便理解

  • 第一次取出的 e 是 B2单元格

  • Set s = e.MergeArea '当前 B2 单元格 的合并区域地址为: $B$2:$B$4

  • If e.Address = s.Range("a1").Address Then '因为 B2 是 合并区域: $B$2:$B$4 中的第1个地址,则执行后面的代码

  • Split(s.Address, "$") '用 $ 拆分合并区域地址, 拆分后:

  • 求和区 = Replace(s.Address, 拆分(1), 替换列)

  • '原来的地址: $B$2:$B$4, 拆分(1)的值: B 见上图, 替换成之前输入的列号: A

  • 替换后的结果: $A$2:$A$4

  • e.Formula = "=sum(" & 求和区 & ")" 'e 即B2单元格 写入公式 =sum($a$2:$a$4)

  • 第二次取出的 e 是 B3单元格

  • If e.Address = s.Range("a1").Address Then '因为 B3 不是 合并区域: $B$2:$B$4 中的第1个地址,则不执行后面的代码

  • 就是通过这样一个循环,完成所有原生公式的写入,完成求和的目的

(0)

相关推荐