实例01-删除多余空行

Private Sub CommandButton处理_Click()

'判断工作簿名，工作表名不为空

With ThisWorkbook.Worksheets("操作界面")

     If Trim(.Cells(2, "C").Value) = "" Or Trim(.Cells(6, "C").Value) = "" Then

     MsgBox "工作簿名称和工作表名称不能为空"

     Exit Sub

     End If

On Error GoTo 处理出错

'定义存储工作簿工作表名称变量

Dim wbname As String

Dim shname As String

wbname = Trim(.Cells(2, "C").Value)

shname = Trim(.Cells(6, "C").Value)

End With

'处理表格

With Workbooks(wbname).Worksheets(shname)

'获得数据区域最大行列号

Dim rmax As Long

Dim cmax As Long

rmax = .UsedRange.Cells(.UsedRange.Count).Row

cmax = .UsedRange.Cells(.UsedRange.Count).Column

'循环判断（反向）

Dim i, icmax

For i = rmax To 1 Step -1

    icmax = .Cells(i, cmax + 1).End(xlToLeft).Column

    If icmax = 1 And .Cells(i, 1) = "" Then     '满足此条件为空行

    '删除空行

    .Rows(i).Delete

    End If

Next i

End With

MsgBox "处理完成"

Workbooks(wbname).Worksheets(shname).Activate

Exit Sub

处理出错:

MsgBox Err.Description

End Sub

实例02-删除多余空列

Private Sub CommandButton处理_Click()

'判断工作簿名，工作表名不为空

With ThisWorkbook.Worksheets("操作界面")

     If Trim(.Cells(2, "C").Value) = "" Or Trim(.Cells(6, "C").Value) = "" Then

     MsgBox "工作簿名称和工作表名称不能为空"

     Exit Sub

     End If

On Error GoTo 处理出错

'定义存储工作簿工作表名称变量

Dim wbname As String

Dim shname As String

wbname = Trim(.Cells(2, "C").Value)

shname = Trim(.Cells(6, "C").Value)

End With

'处理表格

With Workbooks(wbname).Worksheets(shname)

'获得数据区域最大行列号

Dim rmax As Long

Dim cmax As Long

rmax = .UsedRange.Cells(.UsedRange.Count).Row

cmax = .UsedRange.Cells(.UsedRange.Count).Column

'循环判断（反向）

Dim i, icmax

For i = cmax To 1 Step -1

    icmax = .Cells(rmax + 1, i).End(xlUp).Row

    If icmax = 1 And .Cells(1, i) = "" Then    '满足此条件为空列

    '删除空列

    .Columns(i).Delete

    End If

Next i

End With

MsgBox "处理完成"

Workbooks(wbname).Worksheets(shname).Activate

Exit Sub

处理出错:

MsgBox Err.Description

End Sub