Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Finding the Size of Individual Worksheets.
by Allen Wyatt
(last updated June 23, 2018)
Maarten wonders if there is a way to find out the size of each worksheet in a workbook. He has a workbook with almost 100 worksheets and he wants to reduce the size of the workbook file. However, he doesn't know which worksheets are the biggest ones in size.
Figuring out the "size" of individual worksheets depends, in large part, on what is meant by "size." Does it mean the number of cells used? The columns and rows used? How much text is stored in the worksheet? The list of metrics could go on and on.
The problem is that questions such as these miss the mark; a worksheet can have many, many items stored on it. For instance, it could contain comments, formulas, text, charts, sound files, and any number of other items. One chart may be larger than another in terms of numbers of cells, but the other could be larger in terms of objects (such as charts or PivotTables).
The only real way to compare relative sizes of worksheets is to save each worksheet out into its own workbook and then examine the size of each resulting workbook. This obviously doesn't answer precisely how large each individual worksheet is because the act of saving a workbook introduces additional overhead into the saved file. However, if each worksheet is saved in the same way, each one will have comparable overhead and thus can be compared to each other to see which is larger.
The following macro adds a worksheet to the current workbook in order to record the sizes of each workbook created. It then steps through each worksheet and saves it into an individual workbook. The size of the workbook is then determined, recorded, and the new workbook deleted.
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
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11112) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Finding the Size of Individual Worksheets.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
If you need to work on two worksheets in the same workbook at the same time, Excel makes this rather easy to do. All you ...Discover More
Moving between to adjacent worksheets is easy; Excel provides a shortcut key to do the trick. If you want to move between ...Discover More
When processing workbook information in a macro, you may need to step through each worksheet to make some sort of ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.