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.