ExcelVBA批量合并或取消单元格

【问题】烦人的合并单元格，我们在进行vlookup、sum等计算中最怕就是遇到神人交过来的表格，

【解决】如果用手工进行取消合并单元格，时间多，

还是用VBA来解决吧！

一、合并单元格

=====代码==========

Sub 输入列进行合并单元格()

Dim rng_in As Range

Dim col_in, i%

Setrng_in = Application.InputBox("请框选拆分依据列！只能选择单列单元格区域！", Title:="提示", Type:=8)

col_in = rng_in.Column

MsgBox "你将对第 " & col_in &" 列进行合并单元格"

Application.DisplayAlerts = False

For i = Cells(Rows.Count, col_in).End(xlUp).Row To 2 Step -1

If Cells(i, col_in).Value = Cells(i - 1, col_in).Value Then

Cells(i - 1, col_in).Resize(2, 1).Merge

With Cells(i - 1, col_in)

.VerticalAlignment = xlCenter

.HorizontalAlignment = xlCenter

End With

End If

Next

Application.DisplayAlerts = True

End Sub

===效果图=====

二、取消合并单元格并填充

1. 选定列取消合并单元格并填充

=====代码==========

Sub 选定列取消合并单元格并填充()

Dim rng_in As Range

Dim col_in, i%

Set rng_in = Application.InputBox("请框选拆分依据列！只能选择单列单元格区域！", Title:="提示", Type:=8)

col_in = rng_in.Column

MsgBox "你将对第 " & col_in &" 列进行取消合并单元格"

With Columns(col_in)

.UnMerge

.SpecialCells(4) = "=R[-1]C"

.Value = .Value

End With

End Sub

===效果图=====

2.选定的区域取消合并单元格并填充

=====代码==========

Sub 选定的区域取消合并单元格并填充()

Dim rng_in As Range

Dim col_in, i%

Dim Rng As Range, V As Range

Set rng_in = Application.InputBox("请框选拆分依据列！只能选择单列单元格区域！", Title:="提示", Type:=8)

For Each Rng In rng_in

If Rng.MergeCells Then

Set V = Rng.MergeArea

Rng.MergeCells = False

V.Value = Rng.Value

V.HorizontalAlignment = xlCenter

End If

Next

End Sub

===效果图=====

=========学习中===========