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.


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 _
  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) _
      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, _
        'add hyperlink to sheet name
        .Hyperlinks.Add _
            Anchor:=.Cells(lCount, 1), _
            Address:="", _
            SubAddress:="'" & ws.Name _
                & "'!A1", _
            ScreenTip:=ws.Name, _
        'add hyperlink to last cell
        .Hyperlinks.Add _
            Anchor:=.Cells(lCount, lFields), _
            Address:="", _
            SubAddress:="'" & ws.Name _
                & "'!" & strLC, _
            ScreenTip:=strLC, _
        lCount = lCount + 1
      End With
    End If
  Next ws
With wsTemp
    .Range(.Cells(1, 1), .Cells(1, lFields)) _
    .Rows(1).Font.Bold = True
End With

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


List Excel Sheets With Used Range

You may also like...

6 Responses

  1. AlexJ says:

    Very useful, Debra (as always). I’m going to put this in my Personal.xlsm file
    The routine I use simply selects the used range on the active sheet. Useful, but not comprehensive like yours. -)

  2. Thanks Deb! This is really helpful. I used to test each sheet with control end.

  3. Ian says:

    Very useful code, many thanks. I’ve already amended it to add an extra column to indicate whether the sheet is hidden or visible. One very minor thing. Code assumes there is a shape on every sheet. If there isn’t the shapes column fills with the information from the last sheet that did have them. I just changed

    If ws.ProtectContents = True Then


    If ws.ProtectContents = True Or ws.Shapes.Count = 0 Then

    to get round this.

Leave a Reply

Your email address will not be published. Required fields are marked *