excel用VBA删除空行和空列

来源:互联网 时间:2016-01-03

在Excel中删除空行和空列的方法有很多,下面的方法是用VBA代码来删除工作表指定区域中的空行和空列:

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

j = 0

For i = lnLastRow To 1 Step -1

If Application.CountA(rnArea.Rows(i)) = 0 Then

rnArea.Rows(i).Delete

j = j + 1

End If

Next i

rnArea.Resize(lnLastRow - j).Select

Application.ScreenUpdating = True

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

j = 0

For i = lnLastColumn To 1 Step -1

If Application.CountA(rnArea.Columns(i)) = 0 Then

rnArea.Columns(i).Delete

j = j + 1

End If

Next i

rnArea.Resize(, lnLastColumn - j).Select

Application.ScreenUpdating = False

End Sub

在运行代码前,先选择需要删除空行和空列的区域。如果要删除空行,则运行Delete_Empty_Rows(),指定区域中的空行将被删除。注意所谓空行是在指定区域中的,如果在指定区域内是空行,而在指定区域外不是空行,那么这些空行还是将会被删除。删除空列则运行Delete_Empty_Columns()。

 

相关阅读:
Top