Excel

True Used Range in Excel

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Jacob Hilderbrand

Description:

This user-defined function determines the actual used range on a worksheet. 

Discussion:

Sometimes Excel cannot properly calculate what the used range is. Some data may have been cleared, however, Excel still thinks that those cells are being used. You want to work with the used range in Excel, but you want to first make sure that it is the actual current used range. This method is more accurate than just using Sheet1.UsedRange 

Code:

instructions for use

			

Option Explicit Sub FindUsedRange() Dim Rng1 As Range Set Rng1 = RealUsedRange If Rng1 Is Nothing Then MsgBox "There is no used range, the worksheet is empty." Else MsgBox "The real used range is: " & Rng1.Address End If End Sub Public Function RealUsedRange() As Range Dim FirstRow As Long Dim LastRow As Long Dim FirstColumn As Integer Dim LastColumn As Integer On Error Resume Next FirstRow = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)) On Error Goto 0 End Function

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. From the Menu, choose Insert-Module.
  5. Paste the code into the right-hand code window.
  6. Close the VBE, save the file if desired.
 

Test the code:

  1. Click on the worksheet in which you want to find the actual used range.
  2. Hit Tools-Macro-Macros and double-click FindUsedRange
 

Sample File:

UsedRange.ZIP 7.73KB 

Approved by mdmackillop


This entry has been viewed 464 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express