【實(shí)例10-刪除多行多列】Excel表格VBA編程實(shí)例 代碼分享
清空參數(shù)
Private Sub CommandButton清空參數(shù)_Click()
With ThisWorkbook.Worksheets("刪除行列")
.UsedRange.ClearFormats
.UsedRange.ClearContents
End With
End Sub
刪除列
Private Sub CommandButton刪除列_Click()
'判斷工作簿名，工作表名不為空
With ThisWorkbook.Worksheets("操作界面")
If Trim(.Cells(2, "C").Value) = "" Or Trim(.Cells(6, "C").Value) = "" Then
MsgBox "參數(shù)不能為空"
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 ThisWorkbook.Worksheets("刪除行列")
'獲得數(shù)據(jù)區(qū)域最大行號(hào)
Dim cmax As Long
cmax = .UsedRange.Cells(.UsedRange.Count).Column
'循環(huán)判斷（反向）
Dim i
For i = cmax To 1 Step -1
If .Cells(1, i) <> "" Then
Workbooks(wbname).Worksheets(shname).Columns(i).Delete '刪除列
End If
Next i
End With
Workbooks(wbname).Save
MsgBox "處理完成"
Workbooks(wbname).Activate
ActiveWindow.WindowState = xlMaximized
Workbooks(wbname).Worksheets(shname).Activate
Workbooks(wbname).Worksheets(shname).Cells(1, 1).Select
Exit Sub
處理出錯(cuò):
MsgBox Err.Description
End Sub
刪除行
Private Sub CommandButton刪除行_Click()
'判斷工作簿名，工作表名不為空
With ThisWorkbook.Worksheets("操作界面")
If Trim(.Cells(2, "C").Value) = "" Or Trim(.Cells(6, "C").Value) = "" Then
MsgBox "參數(shù)不能為空"
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 ThisWorkbook.Worksheets("刪除行列")
'獲得數(shù)據(jù)區(qū)域最大行號(hào)
Dim rmax As Long
rmax = .UsedRange.Cells(.UsedRange.Count).Row
'循環(huán)判斷（反向）
Dim i
For i = rmax To 1 Step -1
If .Cells(i, 1) <> "" Then
Workbooks(wbname).Worksheets(shname).Rows(i).Delete '刪除行
End If
Next i
End With
Workbooks(wbname).Save
MsgBox "處理完成"
Workbooks(wbname).Activate
ActiveWindow.WindowState = xlMaximized
Workbooks(wbname).Worksheets(shname).Activate
Workbooks(wbname).Worksheets(shname).Cells(1, 1).Select
Exit Sub
處理出錯(cuò):
MsgBox Err.Description
End Sub
