哈洞数据处理公司日常数据处理工作必备代码

此篇整理的是对工作表一些简单操作的代码，仅供小白参考。

删除工作表(Delete方法)

Sub DeleteWorksheet()

MsgBox "删除当前工作簿中的工作表sheet2"

Application.DisplayAlerts = False

Worksheets("sheet2").Delete

Application.DisplayAlerts = True

End Sub

说明：代码使用Application.DisplayAlerts= False来屏蔽弹出的警告框。

判断一个工作表(名)是否存在

Sub testWorksheetExists1()

Dim ws As Worksheet

If Not WorksheetExists(ThisWorkbook,"sheet1") Then

MsgBox "未找到该工作表", vbOKOnly

Exit Sub

End If

MsgBox "找到工作表"

Set ws =ThisWorkbook.Worksheets("sheet1")

End Sub

Function WorksheetExists(wb As Workbook, sName As String) As Boolean

Dim s As String

On Error GoTo ErrHandle

s = wb.Worksheets(sName).Name

WorksheetExists = True

Exit Function

ErrHandle:

WorksheetExists = False

End Function

说明：在测试代码中，用相应的工作簿名和工作表名分别代替“ThisWorkbook”和“Sheet1”，来判断指定工作表是否在工作簿中存在。

排序工作表

Sub SortWorksheets1()

Dim bSorted As Boolean

Dim nSortedSheets As Long

Dim nSheets As Long

Dim n As Long

nSheets = Worksheets.Count

nSortedSheets = 0

Do While (nSortedSheets>

bSorted = True

nSortedSheets = nSortedSheets + 1

For n = 1 To nSheets - nSortedSheets

If StrComp(Worksheets(n).Name, Worksheets(n + 1).Name,vbTextCompare) > 0 Then

Worksheets(n + 1).Move Before:=Worksheets(n)

bSorted = False

End If

Next n

Loop

End Sub

说明：代码采用了冒泡法排序。

Sub SortWorksheets2()

'根据字母对工作表排序

Dim i As Long, j As Long

For i = 1 To Sheets.Count

For j = 1 To Sheets.Count - 1

If UCase$(Sheets(j).Name) > UCase$(Sheets(j +1).Name) Then

Sheets(j).Move After:=Sheets(j + 1)

End If

Next j

Next i

End Sub

删除当前工作簿中的空工作表

Sub Delete_EmptySheets()

Dim sh AsWorksheet

For Each sh In ThisWorkbook.Worksheets

If Application.WorksheetFunction.CountA(sh.Cells) = 0Then

Application.DisplayAlerts = False

sh.Delete

Application.DisplayAlerts = True

End If

Next

End Sub

其实呢，我整理的这些都是些以前学习的摘记，如果读者有心，可以很容易找到这些代码的原文。

基础不好，完全靠自学进度还是很慢的，难度不言而喻。