在exce中删除空行和空列的方法有很多,相对而言删除空行较为简单,只需进行筛选,将空白行筛选出来,删除即可,但要删除空列比较困难。因为你不能按列进行筛选删除。Excel中没有这个功能。当然你可以用另外一种方法,就是按列进行排序将空白列排序在一起,然后删除,但这种方法面临着列的顺序被打乱的问题,吃力不讨好。本文将利用Excel中强大VBA功能来完成。
假设要删除如下图中的空白行或空白列:
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110200166-15693768.png)
文件-》选项-》自定义功能区-》主选项卡-》开发工具
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110201760-1406759806.png)
插入按钮
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110202166-543115533.png)
表单控件或者ActiveX控件都可以。
先以表单控件介绍:
Alt+F11 打开vb宏编辑器,插入模块,并输入如下代码:
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110202713-2126602378.png)
'删除空行
Sub DeleteEmptyRows()
Dim LastRow As Long, r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
For r = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub
'删除空列
Sub DeleteEmptyColumns()
Dim LastColumn As Long, c As Long
LastColumn = ActiveSheet.UsedRange.Columns.Count
LastColumn = LastColumn + ActiveSheet.UsedRange.Column
For c = LastColumn To 1 Step -1
If WorksheetFunction.CountA(Columns(c)) = 0 Then Columns(c).Delete
Next c
End Sub
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110727495-2121979485.png)
关闭编辑器后,在界面拖画一个按钮,在弹出的窗口中选中DeleteEmptyColumns(即刚才插入的删除空列)函数方法。
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110204041-327054940.png)
右键刚才拖出的按钮,右键 编辑文字 修改为 删除空列
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110204448-1580892612.png)
之后在非"设计模式"下,点击测试
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110205198-373370395.png)
同样的,添加 删除空行按钮
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110205666-435440260.png)
保存时,会弹出警告:
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110206088-1614209563.png)
因此需要另存为"启用宏的工作簿"。
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110206385-84752280.png)
然后保存。
再以ActiveX控件为例:
拖出按钮后,打开设计模式
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110206807-1643302267.png)
在设计模式下,右键修改button的名称,即Caption属性
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110207307-526159985.png)
双击打开消息框
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110207885-356540882.png)
在打开的编辑器中,输入
MsgBox ("消息框")
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110208495-1668512212.png)
关闭设计模式和编辑器,点击按钮测试
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110208901-926779812.png)
同样的可以创建两个ActiveX按钮,完成删除所选区空列和删除所选区空行
下面的方法是用VBA代码来删除工作表指定区域中的空行和空列: 在运行代码前,先选择需要删除空行和空列的区域。如果要删除空行,则运行Delete_Empty_Rows(),指定区域中的空行将被删除。
注意所谓空行是在指定区域中的,如果在指定区域内是空行,而在指定区域外不是空行,那么这些空行还是将会被删除。删除空列则运行Delete_Empty_Columns()。
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110209291-1336531960.png)
不过,此刻的代码作用在Sheet1 上
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110209745-1061560019.png)
Private Sub CommandButton1_Click()
MsgBox ("消息框")
End Sub
Private Sub CommandButton2_Click()
Delete_Empty_Rows
End Sub
Private Sub CommandButton3_Click()
Delete_Empty_Columns
End Sub
'Option Explicit
'删除所选区空行
Sub Delete_Empty_Rows()
Dim rnArea As Range
Dim lnLastRow As Long, i As Long, j As Long
Application.ScreenUpdating = False
lnLastRow = Selection.Rows.Count
Set rnArea = Selection
If rnArea.Rows.Count 0 Then
rnArea.Resize(lnLastRow - j).Select
End If
Application.ScreenUpdating = True
LastLine:
End Sub
'删除所选区空列
Sub Delete_Empty_Columns()
Dim lnLastColumn As Long, i As Long, j As Long
Dim rnArea As Range
Application.ScreenUpdating = False
lnLastColumn = Selection.Columns.Count
Set rnArea = Selection
If rnArea.Columns.Count 0 Then
rnArea.Resize(, lnLastColumn - j).Select
End If
Application.ScreenUpdating = False
LastLine:
End Sub
调整好按钮位置就可以用了。不过有时候按钮会挡住文字部分,所以可以把它移动到快速访问工具栏上。
自定义到工具栏,快速访问
excel选项-自定义-从下列位置选择命令-选择宏-》STEP添加到自定义快速访问工具栏中
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110210323-25620955.png)
![](https://images2015.cnblogs.com/blog/572188/201703/572188-20170315110210729-222584140.png)
附件
|