List All Excel Sheets With Used Range
Last week I was updating one of my Excel sample files, and noticed that it was way bigger than it should be. Most of the sample files are just a few kilobytes in size, but this one was about 1.5 MB. What was going on?
Here's how I found the problem, and a macro that you can use for troubleshooting in your workbooks.
Check the Sheets
An Excel file that's 1.5 MB might not seem too alarming, but I like to keep the sample files small, so people can download them quickly. If you downloaded that big file, I'm sorry!
First, I took a quick look at the sheets in the workbook, to see if there was an obvious problem. Nothing looked unusual – other sample files had similar data and reports, but were a much smaller file size.
So, I moved on to phase 2 of the troubleshooting.
Find the Last Cell
As you probably know, some sheets look like they only have a bit of data on them. However, if you press Ctrl + End, to go to the last used cell, you end up way out in the wilderness. You could land in the last column, or down thousands of rows, far away from any of the data on the worksheet.
Maybe there used to be data there, or something was accidentally formatted, long ago. Now it's gone, but Excel remembers that it was used once.
List the Used Range
I wanted to check the used ranges, but the sample file had several sheets. I really didn't want to test each sheet individually, with the Ctrl + End shortcut. Who has time for that?
To save time, I used the Sheet Info tool in my Excel Tools add-in, and it showed that one of the sheets had a used range that ended in the last row! No wonder the file was so big.
Fix the Problem
To fix it, I manually deleted all the unused rows, and saved the file. There are instructions on my website, along with sample code for programmatically resetting the used range. After that, the file size went down to about 40 KB.
You can see the much smaller used range in the list below. Now there are only 420 cells in the used range, instead of 15.7 million!
Code to List Excel Sheets With Used Range
If you don't have a copy of my Excel Tools, you can use the following macro. It adds a sheet to the active workbook, and lists all the sheets, along with their used range address, and the number of cells in the used range. It also lists the cells at the top left corner of any shapes on the sheet.
You can click the links to go to the last cell, or to any of the worksheets.
Copy to an Excel workbook, and save that file in xlsm or xlsb format.
Sub ListSheetsRangeInfo() Dim ws As Worksheet Dim lCount As Long Dim wsTemp As Worksheet Dim rngF As Range Dim lFields As Long Dim strLC As String Dim strSh As String Dim sh As Shape Application.EnableEvents = False Application.ScreenUpdating = False On Error Resume Next Set wsTemp = Worksheets _ .Add(Before:=Sheets(1)) lCount = 2 lFields = 5 With wsTemp .Range(.Cells(1, 1), _ .Cells(1, lFields)).Value _ = Array( _ "Sheet Name", _ "Used Range", _ "Range Cells", _ "Shapes", _ "Last Cell") End With For Each ws In ActiveWorkbook.Worksheets If ws.Name <> wsTemp.Name Then strSh = "" strLC = ws.Cells _ .SpecialCells(xlCellTypeLastCell) _ .Address If ws.Shapes.Count > 0 Then For Each sh In ws.Shapes strSh = strSh & sh.TopLeftCell _ .Address & ", " Next sh strSh = Left(strSh, Len(strSh) - 2) End If With wsTemp .Range(.Cells(lCount, 1), _ .Cells(lCount, lFields)).Value _ = Array( _ ws.Name, _ ws.UsedRange.Address, _ ws.UsedRange.Cells.Count, _ strSh, _ strLC) 'add hyperlink to sheet name .Hyperlinks.Add _ Anchor:=.Cells(lCount, 1), _ Address:="", _ SubAddress:="'" & ws.Name _ & "'!A1", _ ScreenTip:=ws.Name, _ TextToDisplay:=ws.Name 'add hyperlink to last cell .Hyperlinks.Add _ Anchor:=.Cells(lCount, lFields), _ Address:="", _ SubAddress:="'" & ws.Name _ & "'!" & strLC, _ ScreenTip:=strLC, _ TextToDisplay:=strLC lCount = lCount + 1 End With End If Next ws With wsTemp .Range(.Cells(1, 1), .Cells(1, lFields)) _ .EntireColumn.AutoFit .Rows(1).Font.Bold = True End With Application.EnableEvents = True Application.ScreenUpdating = True End Sub