VBA code to calculate the size of each sheet in a workbook?

[krodriguez]

Hello-

I need to know what sheets are the most heavy in my workbook. Anyone with VBA code to calculate the size of each sheet in a workbook?

 

[lrobbo314]

If by the size of the sheet you just mean, how many cells are in the used range, then this code can do it.

Sub SheetSizes()
Dim ws As Worksheet


With CreateObject("System.Collections.ArrayList")
    For Each ws In ActiveWorkbook.Worksheets
        .Add ws.Name & " " & ws.UsedRange.Cells.Count & " cells used"
    Next ws
    sn = .toarray
    MsgBox Join(sn, vbLf)
End With


End Sub

 

[krodriguez]

If by the size of the sheet you just mean, how many cells are in the used range, then this code can do it.

Sub SheetSizes()
Dim ws As Worksheet


With CreateObject("System.Collections.ArrayList")
    For Each ws In ActiveWorkbook.Worksheets
        .Add ws.Name & " " & ws.UsedRange.Cells.Count & " cells used"
    Next ws
    sn = .toarray
    MsgBox Join(sn, vbLf)
End With


End Sub

I should be more specif, by size I mean how much data (kb) is in each sheet.

 

[lrobbo314]

Looks like ZVI has a solution here.

 

[krodriguez]

That code didn't work, actually did not run at all