CharlieBrownFinal

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.

UsedRange1

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.

UsedRange2

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:

UsedRangeError1

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?

, , , , ,

SNLFinal

In Coffee Talk on Satruday Night Live, Linda Richman, played by Mike Myers would hurl out “Discussion Topics”. Here are a few of my favorites:

  1. “The Partridge Family were neither partridges, nor a family. Discuss.”
  2. “The radical reconstruction of the South after the Civil War was neither radical nor a reconstruction. Discuss.”
  3. “The jelly bean is neither made of jelly nor is it a bean. Discuss.”

However, today’s topic is not about SNL or the radical reconstruction of the south. It is about Excel.

Today’s discussion topic:

In their landmark tome, Professional Excel Development, 2nd Edition, Bovey et. al. contend that Row 1 and Column A should be left empty (Bovey et. al. pg. 70). I posit that it is fine to use Row 1 Column A (R1C1) for tabs in the Data Layer of the Workbook – Discuss.

,