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
      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

_____________________

List Excel Sheets With Used Range

6 comments to List All Excel Sheets With Used Range

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>