[Share the results, rejoice in positive energy] People will lose in the word "wait" for the rest of their lives. They will not be busy. Wait for the next time, wait for time and conditions, and life cannot withstand the waiting. Wake up, don't wait until you get old before you understand...
"VBA Code Solution" (10028096) This set of tutorials is the earliest tutorial I launched, and it has been revised in the third edition. This set of tutorials is positioned as an improvement after getting started. In the process of learning this set of tutorials, the focus is to understand and master my " building block programming" idea . You should flexibly use examples in the tutorial like building blocks to arrange your favorite code. The set of tutorials for
is a total of three volumes, with 147 lectures, with a wide coverage of content and is also an excessive tutorial between the primary and intermediate levels. The revised content mainly provides program source code files and code corrections to 32-bit and 64-bit mixed-use code. It will be launched for everyone in the future. Today's content is Lecture 57: VBA determines whether the worksheet is empty, and delete
after it meets the content. Lecture 57: Write a segment of VBA code, determine whether the worksheet is an empty table, and then delete
Hello everyone, we will continue to explain Lecture 57 of the VBA code solution today: determine whether the worksheet is an empty table. In actual work, we need to often judge whether a worksheet is empty, so how can this be done in VBA?
1 How to determine if the worksheet is empty. There are no special properties or functions in
VBA. You can determine whether the worksheet is a blank worksheet. You can use a custom function to return the specified worksheet.
- Knowledge point 1: Use the worksheet function CountA to count the number of non-empty cells in the used area of the worksheet: The COUNTA function function returns the number of non-empty cells in the parameter list. The function COUNTA can calculate the number of cells containing data in a cell range or array.
- Knowledge Point 2: We can customize a function to use CountA to count the number of non-empty cells in the used area of the worksheet. If this value is 0, then this worksheet is empty.
First look at our custom functions, as shown in the following code.
Function MyIsBlankSht(Sh As Variant) As Boolean
If TypeName(Sh) = "String" Then Set Sh = Worksheets(Sh)
If Application.CountA(Sh.UsedRange.Cells) = 0 Then
MyIsBlankSht = True
End If
End Function
code screenshot:
code analysis: Custom MyIsBlankSht function contains a parameter of the Variant variable type, representing the worksheet name or object name. If the specified worksheet is an empty worksheet, the function returns True.
1) The second line of code uses the TypeName function to determine whether the parameter Sh is of string type ("String"). If it is a string, the worksheet with the string as its name will be assigned to the variable Sh.
2) The third line of code uses the worksheet function CountA to count the number of non-empty cells in the worksheet that has been used in the worksheet. If the statistics result is 0, it means that the worksheet is an empty worksheet.
2 Code and code to determine that the worksheet is empty.
Let’s take a look at the actual use of the above custom functions: With the above custom functions, you can now use the customized MyIsBlankSht function just like using VBA functions. For example, in the following code, we need to first determine whether a worksheet is empty. If it is empty, then delete it.
Sub mynz_57 ()
Dim Sh As Worksheett
Application.DisplayAlerts = False
i = 1
For Each Sh In ThisWorkbook.Sheets
If MyIsBlankSht(Sh) Then Sh.Delete: MsgBox "Delete" & i & "Sheets": i = i + 1
Nextml5
Application.DisplayAlerts = True
MsgBox "Delete in total" & i - 1 & "Tables!"
End Sub
Code screenshot:
Code analysis: Use the custom MyIsBlankSht function to delete all empty worksheets in the workbook.
1) The third line of code sets the DisplayAlerts property of the Application object to False, so that the system warning dialog box is not displayed when deleting.
2) Line 5 to line 7 code, use the For Each...Next statement to traverse all worksheets, use the custom MyIsBlankSht function to determine whether it is an empty table, and if it is an empty table, use the Delete method to delete it.
- Note: The custom MyIsBlankSht function only determines whether the content of the cell area of the worksheet is empty. If there are other objects in the worksheet (such as graphic objects, data validity, cell annotations, etc.), it is not used as a benchmark for judgment.
Code running:
Finally, we will tell us how many worksheets have been deleted in total:
Today's content dedication:
1 How to judge that the worksheet is empty? (Application.CountA(Sh.UsedRange.Cells) = 0)
2 How to delete a blank worksheet? (Sh.Delete)
Content reference program file for this lecture: VBA code solution (55-60).xlsm
(Note: Before testing this lecture program, be sure to backup the original file!)
My more than 20 years of VBA practical experience is condensed in the following tutorials:
[Share the results, rejoice in positive energy] The environment is created by the heart, and things are transformed with the heart. Everything now, whether it is good or bad, will eventually pass. Instead of being full of sorrow and anxiety, it is better to be optimistic and face everything calmly, let your heart be filled with sunshine and dispel the mental friction caused by all negative emotions. Please believe that everything will be fine. .
