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.

List Excel Sheets With Used Range

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.

listsheetsusedrange02

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!

listsheetsusedrange04

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.

listsheetsusedrange01

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
      strLC = ws.Cells _
        .SpecialCells(xlCellTypeLastCell) _
          .Address
      If ws.ProtectContents = True Then
        strSh = ""
      Else
        If ws.Shapes.Count > 0 Then
          strSh = ""
          For Each sh In ws.Shapes
            strSh = strSh & sh.TopLeftCell _
                .Address & ", "
          Next sh
          strSh = Left(strSh, Len(strSh) - 2)
        End If
      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

_____________________

List Excel Sheets With Used Range

One thought on “List All Excel Sheets With Used Range”

  1. Very nice script. Very useful
    I have something extra for the script.
    I had an excel file with 20000 Conditional Formatting (after numerous copy/paste) which was unworkable. This addition to the script shows the amount of Conditional Formatting rules and also the amount of shapes.
    lFields = 5 => lFields = 7
    After
    “Range Cells”, _
    2 extra lines
    “Count Conditional Formatting”, _
    “Count Shapes”, _
    After
    ws.UsedRange.Cells.Count, _
    2 extra lines
    ws.Cells.FormatConditions.Count, _
    ws.Shapes.Count, _

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.