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.
4K+ Views
