Here in the States – All Hallows’ Eve (Halloween) is quickly approaching. One of my favorite times of the year with leaves changing color, daylight getting shorter, a crispness in the air, football season is in full swing, and the Fall Classic (Major League Baseball) begins in a few weeks.
All Hallows’ Eve is a time for hobgobblery and apparitions of all sorts as with Charlie Brown and the Peanuts gang pictured here. Excel too has a ghost. In this post I’ll take a look at one and how we might put it to rest.
Worksheet UsedRange Property
The Worksheet Object has a UsedRange Property. Normally, we should be able to use this property so that we can quickly identify the entire UsedRange on the Worksheet without having to jump through a lot of hoops.
Here is a Range of Cells with some data. We can quickly find the address of the UsedRange on the Worksheet:
Option Explicit
Sub GetUsedRangeAddress()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set rng = ws.UsedRange
Debug.Print rng.Address
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Output:
$A$1:$J$10
Great – that is what I expected. But what happens if I delete some data?
UsedRange…False/Positive
A false positive occurs when we test for data and Excel tells us that there is data when in fact there is not.
Here is the same data as before, but I deleted the data from Columns $H:$J. Now I’ll respin the code and check results:
$A$1:$J$10
Hmmm…same results – that is not good – therefore:
- we cannot rely on the UsedRange Property of the Worksheet Object. We need a better way to find the TRUE used range of data
The Last Used Cell
We can use the Find Method of the Range Object to get the last cell on the Worksheet that contains any data:
Sub GetLastCell()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
With ws
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
Debug.Print rng.Address
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Output:
$G$10
Great – that’s what I was looking for. What happens if there is nothing on the Worksheet? I moved to a new worksheet in the Workbook and tried the code again:
That’s not good. I need to revise my code a bit to handle cases where there is no data on the worksheet:
Sub GetLastCellHandleNoData()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
With ws
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
If Not rng Is Nothing Then
Debug.Print rng.Address
Else
Debug.Print "There is no data on worksheet ", ws.Name
End If
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
I tested the code on a Worksheet with data in $A$1:$G$10. Results:
$G$10
Great – that’s what I expected.
Next I tested on a Worksheet with no data. Results:
There is no data on worksheet Sheet2
Great – that’s what I expected.
- I tested the code on a wide variety of scenarios for data placement on the Worksheet. It appears to work for any possible scenario. Please let me know if your tests return unexpected results or errors.
I now have the last used Cell on the Worksheet. Now I need the first used Cell on the Worksheet.
The First Used Cell
For the first used Cell, I tested Cell(1,1) first and then the remainder of the Worksheet. When data was in Cell $A$1, beginning at $A$1 and searching was returning the next Cell address.
Sub GetFirstCell()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
With ws
If Not IsEmpty(ws.Cells(1, 1)) Then
Set rng = ws.Cells(1, 1)
Else
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
End With
If Not rng Is Nothing Then
Debug.Print rng.Address
Else
Debug.Print "There is no data on worksheet ", ws.Name
End If
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Final Functions() and Subs()
The stuff above is fine, but it would be cool if we could develop a function to return the True Used Range. I developed a few Functions() and Subs() to that end.
Function..GetUserSelectedCell
I would like to prompt the user to select a cell and test if there is any data on the worksheets the cell is located on. This makes my code more efficient before I continue processing.
Here I am using the InputBox of the Application Object with Type 8 parameter to allow the user to select a cell for the InputBox. More on the Application.InputBox Method (Excel).
Public Function GetUserSelectedCell(strPrompt As String, _
strTitle As String) As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'GetUserSelectedCell
'Returns a Range Object based on Cell user selects
'
'Parameters :
'strPrompt : A string variable.
' : Provide a question or statement to the user to take some action.
'strTitle : A string variable.
' : Provide a title for the InputBox.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Declare variables
Dim rng As Range
'Users - select a cell on a worksheet
On Error Resume Next
Set rng = Application.InputBox( _
Prompt:=strPrompt, _
Title:=strTitle, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection
On Error GoTo 0
'Activate the worksheet
On Error Resume Next
rng.Parent.Activate
'Pass object to function
Set GetUserSelectedCell = rng
'Tidy up
If Not rng Is Nothing Then Set rng = Nothing
End Function
And here is how I call the Function in the final Sub()
'Prompt user to select a cell on a worksheet
Set rngUserCell = GetUserSelectedCell(strPrompt:="Please select a cell on a worksheet.", _
strTitle:="Get Cell Selection From User")
Function..What if the user clicked cancel?
The user may choose to cancel at the InputBox, so we need to handle that possibility. In this Function() I am using a MsgBox to ask the user if they wish to try again. More on the MsgBox Function.
Public Function GetUserMessageResponse(strPrompt As String, _
strTitle As String, _
lngButtons As Long) As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'GetUserMessageResponse
'Returns a value as a Long DataType
'
'Parameters :
'strPrompt : Required. A string datatype.
' : Provide a question or statement to the user to take some action.
'strTitle : A string datatype.
' : Provide a title for the InputBox.
'lngButtons : A long datatype
' : Use one of the vba button type enumerations
' : vbOKOnly 0 OK button only <-Default value
' : vbOKCancel 1 OK and Cancel buttons
' : vbAbortRetryIgnore 2 Abort, Retry, and Ignore buttons
' : vbYesNoCancel 3 Yes, No, and Cancel buttons
' : vbYesNo 4 Yes and No buttons
' : vbRetryCancel 5 Retry and Cancel buttons
'Information : The Message Box returns 1 of 7 values:
' : vbOK 1
' : vbCancel 2
' : vbAbort 3
' : vbRetry 4
' : vbIgnore 5
' : vbYes 6
' : vbNo 7
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Declare variables
Dim MsgBoxValue As Long
'Users - select a cell on a worksheet
MsgBoxValue = MsgBox( _
Prompt:=strPrompt, _
Buttons:=lngButtons, _
Title:=strTitle)
'Handle user actions
If MsgBoxValue <> vbYes Then
MsgBoxValue = vbCancel
End If
'Pass value to function
GetUserMessageResponse = MsgBoxValue
End Function
Function..TestForData
Now that I have a Cell on a Worksheet, I need to test to see if there is any data on the Worksheet. In the Function below, I first check Cell(1,1) for any data, if that does not contain any data, then I check the rest of the worksheet.
Here I am using the Find Method of the Range Object to search for anything on the Worksheet. More on the Range.Find Method (Excel).
Public Function TestForData(ws As Worksheet) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'TestForData
'Returns a boolean datatype
'Checks to see that data exists in at least 1 Cell on a Worksheet
'
'Parameters :
'ws : Required, A Woksheet Object.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Declare variables
Dim rng As Range
'Initialize values
TestForData = False
'Check the worsheet for data
On Error Resume Next
If Not IsEmpty(ws.Cells(1, 1)) Then
Set rng = ws.Cells(1, 1)
Else
With ws
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
End If
'Update function value if the worksheet contains data
If Not rng Is Nothing Then TestForData = True
'Tidy up
Set rng = Nothing
End Function
And here is how I call the Function in the final Sub()
'Check if the worksheet has any data
blnFlag = TestForData(ws:=wsUserCell)
Function..Find First And Last Cells
So far, I have tested if the user clicked cancel or if the worksheet contains any data, at this point, I have passed those tests, so now I can get to the meat of it.
I have one Function to return either the first used Cell or the last used Cell – and that my friends is cool. I want my Functions() to be fast, efficient and flexible. I vary whether the Function() returns the last used Cell or first used Cell by passing a variable to the SearchDirection by using the values of the xlSearchDirection Enumeration: xlNext and xlPrevious.
When the SearchDirection is xlNext, the Function() returns the first used Cell. When the SearchDirection is xlPrevious, the Function() returns the last used Cell.
Public Function GetCell(ws As Worksheet, _
rng As Range, _
lngDirection As Long) As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'GetCell
'Returns a Range Object based on a single Cell
'The Cell is either the first Cell in a Range or the last Cell in a Range
'The distinction is based on the parameter value passed to lngDirection by the user as either xlPrevious or xlNext
'
'Parameters :
'ws : Required, A Woksheet Object.
'rng : Required, A Range Object.
'lngDirection : Required, Either xlNext or xlPrevious.
' Use xlPrevious when searching for the last used Cell.
' Use xlNext when searching or the first used cell.
'
'Use : Find the last used Cell first
' : Pass the last used Cell as a Range Object to the function to determine the first used Cell
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Get range as a single cell
With ws
Select Case lngDirection
Case xlNext
If Not IsEmpty(.Cells(1, 1)) Then
Set rng = .Cells(1, 1)
Else
Set rng = .Cells.Find(What:="*", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=lngDirection, _
MatchCase:=False)
End If
Case xlPrevious
Set rng = .Cells.Find(What:="*", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=lngDirection, _
MatchCase:=False)
End Select
End With
'Pass the range to the function
Set GetCell = rng
'Tidy up
Set rng = Nothing
End Function
When I call the Function() to get the last used Cell, I pass Cell(1,1) to the Function() and appropriate enumeration value for SearchDirection:
'Get last cell
Set rngLastCell = GetCell(ws:=wsUserCell, _
rng:=wsUserCell.Cells(1, 1), _
lngDirection:=xlPrevious)
When I call the Function() to get the first used Cell, I pass the Range Object created in the first call to the Function() and appropriate enumeration value for SearchDirection:
'Get first cell
Set rngFirstCell = GetCell(ws:=wsUserCell, _
rng:=rngLastCell, _
lngDirection:=xlNext)
The Final Sub()…GetTrueUsedRange
And here’s the final Sub() to bring it all together:
Option Explicit
Sub GetTrueUsedRange()
'Declare variables
Dim wb As Workbook
Dim wsUserCell As Worksheet
Dim rngUserCell As Range
Dim rngStart As Range
Dim rngLastCell As Range
Dim rngFirstCell As Range
Dim rngTrueUsedRange As Range
Dim blnDataExists As Boolean
Dim lngMessageResponse As Long
Dim lngFirstCellRow As Long
Dim lngFirstCellColumn As Long
Dim lngLastCellRow As Long
Dim lngLastCellColumn As Long
'Initialize
Set wb = ThisWorkbook
'Prompt user to select a cell on a worksheet
Set rngUserCell = GetUserSelectedCell( _
strPrompt:="Please select a cell on a worksheet.", _
strTitle:="Get Cell Selection From User")
'Get the worksheet that contains the cell the user selected
If Not rngUserCell Is Nothing Then
Set wsUserCell = rngUserCell.Parent
Else
lngMessageResponse = GetUserMessageResponse( _
strPrompt:="The selected worksheet does not contain any data." & vbLf & _
"Or you clicked ""Cancel.""" & vbLf & _
"Would you like to try a different worksheet?", _
strTitle:="Missing Data Warning", _
lngButtons:=vbYesNo)
End If
'Check if the worksheet has any data
blnDataExists = TestForData(ws:=wsUserCell)
'If the worksheet does not have any data, ask the user to select a different worksheet or exit
If blnDataExists = False Then
lngMessageResponse = GetUserMessageResponse( _
strPrompt:="The selected worksheet does not contain any data." & vbCrLf & _
"Would you like to try a different worksheet?", _
strTitle:="Missing Data Warning", _
lngButtons:=vbYesNo)
If lngMessageResponse = vbYes Then
Call GetTrueUsedRange 'Recursive call
Exit Sub
Else
MsgBox "You clicked ""No"" or ""Cancel"". Now exiting.", vbInformation, "No Data Warning"
Exit Sub
End If
Else
'Get last cell
Set rngLastCell = GetCell(ws:=wsUserCell, _
rng:=wsUserCell.Cells(1, 1), _
lngDirection:=xlPrevious)
With rngLastCell
lngLastCellRow = .Row
lngLastCellColumn = .Column
End With
'Get first cell
Set rngFirstCell = GetCell(ws:=wsUserCell, _
rng:=rngLastCell, _
lngDirection:=xlNext)
With rngFirstCell
lngFirstCellRow = .Row
lngFirstCellColumn = .Column
End With
End If
'Create true used range
Set wsUserCell = wb.ActiveSheet
Debug.Print "Worksheet", wsUserCell.Name
With wsUserCell
Set rngTrueUsedRange = .Range(.Cells(lngFirstCellRow, lngFirstCellColumn), _
.Cells(lngLastCellRow, lngLastCellColumn))
End With
'Results
Debug.Print "True used range", rngTrueUsedRange.Address
'Tidy up
If Not rngUserCell Is Nothing Then Set rngUserCell = Nothing
If Not rngStart Is Nothing Then Set rngStart = Nothing
If Not rngLastCell Is Nothing Then Set rngLastCell = Nothing
If Not rngFirstCell Is Nothing Then Set rngFirstCell = Nothing
If Not rngTrueUsedRange Is Nothing Then Set rngTrueUsedRange = Nothing
If Not wsUserCell Is Nothing Then Set wsUserCell = Nothing
If Not wb Is Nothing Then Set wb = Nothing
End Sub
Tidy Up
I tested the Sub() on several different Worksheets with a variety of placement of data, no data and single cells of data. All tests returned correct expected results. Please let me know if your tests return incorrect results.
How do you find the True Used Range on your Worksheets?
