- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Check The Size of Each Worksheet of a Workbook?
It is commonly known that when you right-click on an Excel file, you can see its properties and size. But what about when you want to know the size of individual worksheets within the file?
Accountants and data analysts maintain large Excel workbooks with multiple worksheets. Understanding the file size can help determine the best way to analyze it. Some might want to begin with the heavy worksheet, or some may start with the lighter one. It often turns out that one sheet takes up the majority of the space in the workbook.
This tutorial explains how to identify the size of each worksheet in a workbook.
VBA Code To Determine Worksheet Sizes in Excel Workbook
You have to share an Excel file, but it is too large to send. It contains 7 worksheets, and you wonder which one can be reduced so the task can be done efficiently. Using a Visual Basic Application (VBA) code in Excel, you can quickly determine the size of all worksheets in one independent sheet, regardless of the number of sheets in the workbook.
Step 1 − Open the workbook file you want to determine the size of the worksheets within.
Step 2 − Go to the "Developer" tab and click on the "Visual Basic" option to open the dialog box. Alternatively, you can also open the dialog box by pressing Alt + F11 key.
Step 3 − Once the dialog box appears, right-click on "Microsoft Excel Objects" for more options and Insert → Module.
Step 4 − Type or paste the VBA code to the Module Editor section, which is the white box on the top right side of the screen.
Microsoft Excel VBA code to check the size of worksheets in a workbook −
Sub WorksheetSizes() Dim wks As Worksheet Dim c As Range Dim sFullFile As String Dim sReport As String Dim sWBName As String sReport = "Size Report" sWBName = "Erase Me.xls" sFullFile = ThisWorkbook.Path & _ Application.PathSeparator & sWBName ' Add new worksheet to record sizes On Error Resume Next Set wks = Worksheets(sReport) If wks Is Nothing Then With ThisWorkbook.Worksheets.Add(Before:=Worksheets(1)) .Name = sReport .Range("A1").Value = "Worksheet Name" .Range("B1").Value = "Approximate Size" End With End If On Error GoTo 0 With ThisWorkbook.Worksheets(sReport) .Select .Range("A1").CurrentRegion.Offset(1, 0).ClearContents Set c = .Range("A2") End With Application.ScreenUpdating = False ' Loop through worksheets For Each wks In ActiveWorkbook.Worksheets If wks.Name <> sReport Then wks.Copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs sFullFile ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True c.Offset(0, 0).Value = wks.Name c.Offset(0, 1).Value = FileLen(sFullFile) Set c = c.Offset(1, 0) Kill sFullFile End If Next wks Application.ScreenUpdating = True End Sub
Step 5 − Click on the "Run Program" button, the second button in the panel or press the F5 key to execute the code.
Step 6 − Return to your Excel workbook to see the information about the worksheets.
As instructed in the VBA code, a new sheet is created called "Size Report." It contains a table with size information regarding all the worksheets in the present workbooks. According to the table, Sheet 1 is the smallest worksheet.
Conclusion
Excel is a productivity software where you can enter large amounts of data in one or multiple sheets. It can include comments, list of clients, assignments, research data, formulae, text, charts, sound files, and other types of data. A sheet may be larger in terms of cell count than another, but some may be greater in object count. We use VBA code to consolidate all the size information in one sheet.