實(shí)例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 處理出錯(cuò)

'定義存儲工作簿工作表名稱變量

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)

'獲得數(shù)據(jù)區(qū)域最大行列號

Dim rmax As Long

Dim cmax As Long

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

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

'循環(huán)判斷（反向）

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

處理出錯(cuò):

MsgBox Err.Description

End Sub

實(shí)例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 處理出錯(cuò)

'定義存儲工作簿工作表名稱變量

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)

'獲得數(shù)據(jù)區(qū)域最大行列號

Dim rmax As Long

Dim cmax As Long

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

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

'循環(huán)判斷（反向）

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

處理出錯(cuò):

MsgBox Err.Description

End Sub