生活资讯
excelvba一键删除多个页面空行 实例10-删除多行多列
2023-04-22 07:00  浏览:45

excelvba一键删除多个页面空行 实例10-删除多行多列(1)

清空参数

Private Sub CommandButton清空参数_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 "参数不能为空"

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 ThisWorkbook.Worksheets("删除行列")

'获得数据区域最大行号

Dim cmax As Long

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

'循环判断(反向)

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

处理出错:

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 "参数不能为空"

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 ThisWorkbook.Worksheets("删除行列")

'获得数据区域最大行号

Dim rmax As Long

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

'循环判断(反向)

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

处理出错:

MsgBox Err.Description

End Sub

,
发表评论
0评