NameBadgeArgumentFinal

Being the 2nd in a series of posts on “Names” in Excel.

Excel uses several “Names”. In a previous post, I wrote about Named Formulas. Today, I’ll take a look at Named Arguments.

Named Arguments

Named arguments are that descriptive tags you sometimes see in VBA code snippets. They are not required, hence, “sometimes”. Here’s a sample of a few:

NamedArguments1

In the sample snippet, I created a function to get a Cell as a Range Object from the user at run-time using the Application.InputBox Method. The InputBox Method actually has 1 required parameter and 8 optional parameters – I am only using 3 parameters – 1 required and 2 optional.


    Read more on the Application.InputBox Method here


Named Arguments Are Not Required

As I stated previously, Named Arguments are not required. Here is the Function rewritten without the Named Arguments. I also added a bit of error handling in case the user clicks the cancel button of the InputBox:

Public Function GetSelectedRangeNoNmArgs() As Range

    'Declare variables
        Dim rng                         As Range
    
    'Users - select a cell on a worksheet
        On Error Resume Next
        Set rng = Application.InputBox _
                        ("Please Select Range", _
                         "Range Select", _
                         8)
        If rng Is Nothing Then
            Exit Function
        End If
    
    'Pass the name of the worksheet to the function
        Set GetSelectedRangeNoNmArgs = rng
    
    'Tidy up
        Set rng = Nothing

End Function

Do you see the difference (other than the error handling) ? Here is a comparison of just the 4 lines of the InputBox of both snippets:

'No Named Arguments
Application.InputBox _
                        ("Please Select Range", _
                         "Range Select", _
                         8)

'Even worse - yikes!
Application.InputBox _
                        ("Please Select Range", "Range Select", 8)

'Named Arguments - Best!
Application.InputBox _
                        (Prompt:="Please Select Range", _
                         Title:="Range Select", _
                         Type:=8)

That makes the parameter values a bit more clear, doesn’t it?

Here’s A Dumb Reason

One reason given on MSDN for using Named Arguments is that you can change the order of the parameters to the function. Like this:

'Original parameter order
Application.InputBox _
                        (Prompt:="Please Select Range", _
                         Title:="Range Select", _
                         Type:=8)

'Rearranged parameters
Application.InputBox _
                        (Type:=8, _
                         Prompt:="Please Select Range", _
                         Title:="Range Select")

IMHO, that is a load of hooey. I cannot think of a single valuable reason to do that – can you?

The Real Reason

The real reason to use Named Arguments is that it makes things clearer – they are self-documenting. My example here is a little silly, maybe you are very use to the InputBox Method and you know the order of the parameters and can rattle ’em off in your sleep like I rattle off the batting order of the ’72 Cinci Reds.


    Petition to get Pete Rose reinstated to Major League Baseball here. Pete was one of the greatest to ever play the game.


Tidy Up

I’m sure you can find some Internet Breadcrumbs of mine, where I have not always used Named Arguments. My code and style have evolved and continue to evolve over time. I now always use Named Arguments – I encourage you to do the same.

, , , , , , , ,

PeleFinal

Pele was an amazing soccer (futbol) player. I’m more of an American football fan myself – but I cannot deny the art, grace and class with which Pele played futbol. Pele is pictured here executing a bicycle kick against Belgium in 1968. Maybe what sets Pele and other sports stars apart from the rest is they too begin with the end in mind as suggested by Stephen Covey in The Seven Habits of Highly Effective People.

Today’s post, however, is not about futbol or the latest book being touted by business and leadership pundits. It is about the Goal Seek Method of the Range Object of the Excel Object Model.

Goal Seek

Goal2

Here is a sample formula to calculate Net Sales using 4 inputs: Average Check, Operating Days, Population and Participation. As you see displayed from FORMULATEXT() Function, the 4 inputs are simply multiplied together.


    The FORMULATEXT() function is one of the new functions introduced with Excel 2013. Check them all out here


We can use Goal Seek manually from the Ribbon. In Excel 2013:

Goal3

  1. Click on the Data Tab of the Ribbon
  2. Go to the Data Tools Group
  3. Click on What-If Analysis
  4. Click on Goal Seek

Goal4

I launched Goal Seek. I want to know what participation would need to be to achieve Net Sales of $10K?

Goal5

Holding all other inputs constant, I would need to increase participation from 14.32% to 16.83% to get to $10K in Net Sales. All well and good so far. But what if I need to find values for 12 different months? 52 Weeks? Some other Scenario with 100’s of desired outputs? Time for some VBA!

Goal Seek VBA

Goal6

I cannot find the Goal Seek Method listed as a Member of the Range Class in the Object Explorer in the VBA Editor?


  1. Press [Alt]+[F11] to launch the VBA Editor
  2. Once in the Editor, [F2] to launch the Object Explorer


Hmmm….I’ll check documentation on MSDN:


  1. Object model reference (Excel 2013 developer reference)
  2. Object model reference (Excel 2010 developer reference)
  3. Object model reference (Excel 2003 developer reference) – Compiled Help File


I checked the Developer Reference for Excel 2013 and Excel 2010. I could not find anything on Goal Seek. I downloaded the Excel 2003 Developed Reference as a compiled help file and finally found some documentation on the Goal Seek Method.

Goal7

Goal8

Here’s a quick little snippet I wrote. Even though Goal Seek does not show as a Method of the Range Object in Excel 2013, even though the Goal Seek Method is not documented in the Excel Developer Reference for Excel 2013, it will still work with Excel 2013.

This is not the first time I have seen this. I can’t recall what the last Object and Member were.

Goal Seek Method Requirements

For the Goal Seek Method to work, I must provide the following objects and values:

  1. A Range Object (Cell) that contains a formula
  2. A goal value
  3. And the changing Cell as a Range Object

Well, that makes sense, since the Goal Seek Dialog Box prompted me to manually select or enter those items earlier.

From the requirements list, I see that one strategy would be to use a loop with the .Cells() Property of the Worksheet Object as the Range Objects. With the Cells() Property, I need to pass a Row Index and a Column Index through each iteration of the loop.

The Setup…

Goal9

I added 11 months to the existing data and randomized the participation amounts. Now I would like to find what the participation would need to be for each of the 12 months to increase sales an additional $5K each month.

Goal10

Goal Seek Method…Fixed Range

Here is a basic Goal Seek Snippet that could be used with a fixed Range where the Worksheets and Range Objects are known and do not change:

Option Explicit

Sub SetGoal()

'----------------------------------------------------------------------
'Use Goal Seek Method of the Range Object with a dictator application
'----------------------------------------------------------------------
'Author     : Winston Snyder
'Date       : 3/30/2015
'Website    : http://dataprose.org/
'----------------------------------------------------------------------

    'Declare objects
        Dim wb As Workbook
        Dim ws As Worksheet
  
    'Declare constants
        Const lngROW_SEEK As Long = 4
        Const lngROW_GOAL As Long = 10
        Const lngROW_CHANGE As Long = 5
        Const lngCOL_BEGIN As Long = 6
        Const lngCOL_END As Long = 17
    
    'Declare variables
        Dim i As Long
    
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Sheet1")
    
    'Goal Seek
        With ws
            For i = lngCOL_BEGIN To lngCOL_END
                .Cells(lngROW_SEEK, i).GoalSeek _
                    Goal:=.Cells(lngROW_GOAL, i).Value, _
                    ChangingCell:=.Cells(lngROW_CHANGE, i)
            Next i
        End With
    
    'Tidy up
        Set ws = Nothing
        Set wb = Nothing
End Sub

Goal11

My Change Row was Row 5, “Participation”. Participation has been increased to the point needed to drive Net Sales on Row 4 to the target “New Sales” on Row 10.

All well and good, but what if I want to:

  1. Choose a different set of values pre-programmed values for the the Goal?
  2. Choose a different input/driver Row

I will need something a bit more flexible.

Goal Seek Method…Dynamic Options

One way to make the code snippet a bit more flexible, is to use a Type:=8 InputBox. The Type:=8 InputBox allows the user to select a Cell on a Worksheet. I first introduced this concept in this post on the LIKE Operator.


    More on Application.InputBox Method (Excel) here


I’ll create a Function and call it 3 times to prompt the user to select a cell that begins the Range for

  1. The Range that contains the formula that I am seeking a goal for.
  2. The Range that contains the target value
  3. The Range that contains an input that is to be be changed to arrive at the target value
Public Function GetUserCell(strPrompt As String) As Range
 
    'Declare variables
        Dim rng As Range
     
    'Users - select a cell on a worksheet
        Set rng = Application.InputBox( _
                    Prompt:=strPrompt, _
                    Title:="Select a Cell", _
                    Default:=ActiveCell.Address, _
                    Type:=8) 'Range selection
                     
    'Get the parent worksheet of the selected cell
        Set GetUserCell = rng
     
    'Tidy up
        Set rng = Nothing

 End Function

Now I can create a Sub(), call the function 3 times, and load my 5 variables based on the user selections.

The final dynamic Goal Seek Sub():

Option Explicit

Sub SetGoalDynamic()

'----------------------------------------------------------------------
'Use Goal Seek Method of the Range Object allow user to choose cells in range for:
'     The formula to be used in determining a solution
'     The goal value
'     An input cell that will be altered by Goal Seek to arrive at the desired solution
'----------------------------------------------------------------------
'Author     : Winston Snyder
'Date       : 3/30/2015
'Website    : http://dataprose.org/
'----------------------------------------------------------------------

    'Declare objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rngRowSeek As Range
        Dim rngRowGoal As Range
        Dim rngRowChange As Range
  
    'Declare constants
        Const strROW_SEEK As String = "Please select a cell in the row that contains the formula you will use for Goal Seek."
        Const strROW_GOAL As String = "Please select a cell in the row that contains the Goal Value that you are trying to find a solution for."
        Const strROW_CHANGE As String = "Please select a cell in the row that contains an input value for the formula."
    
    'Declare variables
        Dim lngRowSeek As Long
        Dim lngRowGoal As Long
        Dim lngRowChange As Long
        Dim lngColBegin As Long
        Dim lngColEnd As Long
        Dim i As Long
    
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Sheet1")
        
    'Get user inputs
        Set rngRowSeek = GetUserCell(strPrompt:=strROW_SEEK)
        Set rngRowGoal = GetUserCell(strPrompt:=strROW_GOAL)
        Set rngRowChange = GetUserCell(strPrompt:=strROW_CHANGE)
        
    'Get variable values from user inputs
        lngRowSeek = rngRowSeek.Row
        lngRowGoal = rngRowGoal.Row
        lngRowChange = rngRowChange.Row
        lngColBegin = rngRowSeek.End(xlToLeft).Column + 1 'Offset for labels
        lngColEnd = rngRowSeek.End(xlToRight).Column
    
    'Goal Seek
        With ws
            For i = lngColBegin To lngColEnd
                .Cells(lngRowSeek, i).GoalSeek _
                    Goal:=.Cells(lngRowGoal, i).Value, _
                    ChangingCell:=.Cells(lngRowChange, i)
            Next i
        End With
    
    'Tidy up
        Set rngRowSeek = Nothing
        Set rngRowGoal = Nothing
        Set rngRowChange = Nothing
        Set ws = Nothing
        Set wb = Nothing
        
End Sub

Tidy Up

That’s it for today, hopefully something helpful for you in this post. I use Goal Seek a bit when forecasting and budgeting and my supervisor asks me for some options to achieve certain desired targets or we get push back upstream.

How do you use Goal Seek?

Btw, as of today, Monday, March 30,2015, the first NFL Preseason game is only 132 days away. Sunday night, August 9, 2015. Hang in there!

, , , , , , , , , ,

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?

, , , , ,