An average Microsoft Excel spreadsheet document has 1,102 rows and 18.2 columns.
That’s 470 rows and 25 columns. I had 408 sheets whose last cell was A1, i.e. blank. I attribute that to two things: I probably have quite a few workbooks that are just code; Most of the workbooks I get from other people have a Sheet2 and a Sheet3 that are unused. The default number of sheets for a new workbook is three.
I also had 15 sheets whose last cell is IV65536, which is clearly wrong. That’s a typical problem with using the SpecialCells(xlCellTypeLastCell) method, but I’ll just exclude those from the average.
If I don’t count the empty sheets and the “full” sheets, I get:
Mean: 103 rows and 28 columns
Median: 58 rows and 11 columns
Mode: 59 rows and 11 columns
That’s over about 2,000 files. It’s not all the Excel files I have, it’s just all of them in the MyDocuments folder. If you’d like to see your average, I’ve posted the code I used below. It takes a few minutes to run and I had to click a few dialog boxes that asked me to edit links or start an external application.
Dim sro As Scripting.FileSystemObject
Dim srFolder As Scripting.Folder
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set sro = New Scripting.FileSystemObject
Set srFolder = sro.GetFolder(“C:Documents and SettingsdkMy Documents”)
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Sub GetLastCells(srFolder As Scripting.Folder)
Dim srFile As Scripting.File
Dim srSubFolder As Scripting.Folder
Dim wb As Workbook, sh As Worksheet, rLast As Range
For Each srFile In srFolder.Files
If srFile.Type = “Microsoft Excel Worksheet” Then
Set wb = Workbooks.Open(srFile.Path)
For Each sh In wb.Worksheets
If Not sh.ProtectContents Then
Set rLast = sh.Cells.SpecialCells(xlCellTypeLastCell)
.Offset(1, 0).Value = wb.FullName
.Offset(1, 1).Value = rLast.Address
.Offset(1, 2).Value = rLast.Row
.Offset(1, 3).Value = rLast.Column
For Each srSubFolder In srFolder.SubFolders