VBA Express : Excel - True Used Range in Excel

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:

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

Test the code:

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

Sample File:

UsedRange.ZIP 7.73KB

Approved by mdmackillop

This entry has been viewed 495 times.