Excel VBA 自动删除空白行

Question

想要VBA代码,实现自动删除空白行的功能

要删除WORKBOOK中所有WORKSHEET(总共4个,SHEET1 至 SHEET4)的空白行, 让每份表的内容都是连着的,不会有中间隔一行空的情况出现

注:每份SHEET里,有内容的列号都是:A至M;行号就不固定了,有的一千多行,有的几百行

Answer 1

1、打开要删除空白行的文档。

2、Alt+F11打开VB编辑器，右键新建一个模块。

3、新建模块后，输入

Sub 删除空行()

Dim a

a = 1

Do While Cells(a, "a") <> ""

      If Cells(a, "D") = "" Then

    Rows(a).Select

    Selection.Delete Shift:=xlUp

    Else

    a = a + 1

        End If

  Loop

End Sub。

4、输入代码后，在工具栏中找到开发工具——宏，点击执行命令。

5、找到并执行删除空行的宏，然后点击执行。

6、运行完成后，就可以把空白行删除了。

Answer 2

删除空白行和列的方法：
步骤一、打开excel，按ALT+F11组合建，调出VBA程序窗口
步骤二、在插入菜单中，选择模块，插入一个模块
步骤三、在新建模块中的代码窗口将以下代码复制进去
‘删除空行
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
完成后关闭VBA窗口，选择保存，并保存Excel文件。
步骤四、保持步骤三中的excel文件处于打开状态，打开需要处理excel文件，执行上述两个宏即可。

用VBA删除空行和空列

在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()。

Answer 3

全自动删除所有空行空列代码：

Sub 删除所有空行和空列()
For Each Sheet In Sheets
Sheet.Select
Sheet.UsedRange.UnMerge

Call DeleteEmptyRows
Call DeleteEmptyColumns
Rows.AutoFit
Columns.AutoFit

Next
End Sub

Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

Sub DeleteEmptyColumns()
Dim LastColumn As Long
Dim r As Long
LastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
For r = LastColumn To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(r)) = 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

Answer 4

Sub 删除所有空行和空列()
For Each Sheet In Sheets
Sheet.Select
Sheet.UsedRange.UnMerge
Rem Range("1:5").Delete
Call DeleteEmptyRows
Call DeleteEmptyColumns
Rows.AutoFit
Columns.AutoFit

Next
End Sub

Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

Sub DeleteEmptyColumns()
Dim LastColumn As Long
Dim r As Long
LastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
For r = LastColumn To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(r)) = 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

Answer 5

Sub ClearBlank()

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

上面的这段代码可以帮你删除所有的空白行.但是我不知道能不能帮助你,最好是能把你的附件上传上来本回答被提问者采纳