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?