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