Create Dynamic Range Negotiation Skills with Excel VBA

This code will focus on dynamically defining a range based on user inputs and performing actions like filtering, calculating, or using it for other operations.

Objective

We will create a dynamic range in Excel using VBA that adapts to the data entered by the user. The range will be based on negotiation skills metrics (like performance scores or competency ratings) and automatically adjust to the data entered in the worksheet.

What the Code Will Do

1. Create a Dynamic Range: Automatically adjust the range as new data is entered or removed.
2. Filter Data: It will filter data based on specific criteria, for example, filtering negotiation skills by a certain rating.
3. Display Results: The results will be displayed dynamically based on the range’s content.

Steps

1. Set up the worksheet structure (headers for negotiation skills).
2. Define a dynamic range.
3. Create a VBA procedure to handle the dynamic range and filtering.

Sample Data Structure:

• Column A: Negotiation Skills (Skills names like « Communication », « Problem Solving », « Active Listening », etc.)
• Column B: Skill Ratings (Numeric values representing the ratings)
• Column C: Performance Feedback (Text or notes)

The VBA Code

Sub CreateDynamicRangeAndFilter()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim skillRange As Range
    Dim filterCriteria As String
    Dim filterColumn As Integer   
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to the sheet name you are working on   
    ' Find the last row with data in Column A (Negotiation Skills column)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Create the dynamic range from A1 to the last row
    Set rng = ws.Range("A1:C" & lastRow)   
    ' Create a named dynamic range (optional, if you want to use the range with a name)
    ThisWorkbook.Names.Add Name:="NegotiationSkillsRange", RefersTo:=rng   
    ' Show a message with the defined range
    MsgBox "The dynamic range 'NegotiationSkillsRange' has been created: " & rng.Address, vbInformation   
    ' Ask for filter criteria (e.g., rating above a certain value)
    filterCriteria = InputBox("Enter the minimum skill rating to filter by:", "Filter Criteria", "3")   
    ' Convert filter criteria to numeric value
    If IsNumeric(filterCriteria) Then
        filterCriteria = CInt(filterCriteria)
    Else
        MsgBox "Invalid input. Please enter a valid numeric value."
        Exit Sub
    End If   
    ' Apply filter to column B (Skill Ratings)
    filterColumn = 2 ' Column B is the second column (Skill Ratings)   
    ' Remove any existing filters
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData    
    ' Apply the new filter
    rng.AutoFilter Field:=filterColumn, Criteria1:=">=" & filterCriteria   
    ' Optionally, you can work with the filtered data here, like summarizing it or extracting specific results
    MsgBox "Filtering complete. Data with rating >= " & filterCriteria & " is now displayed."
End Sub

Explanation of the Code

1. Worksheet Setup: The ws variable defines the worksheet to work on (change « Sheet1 » to your actual worksheet name).
2. Finding the Last Row: The lastRow variable dynamically calculates the last row in column A. This ensures that no matter how many rows of data are added or removed, the range always adjusts.
3. Defining the Dynamic Range: The range rng is created from cell A1 to the last row (lastRow) in column C. This means the range will automatically adjust to the number of rows in your data set.
4. Named Dynamic Range: The ThisWorkbook.Names.Add line creates a named dynamic range (optional). This allows you to reference the range by its name (« NegotiationSkillsRange ») elsewhere in your workbook.
5. User Input for Filter Criteria: The InputBox function prompts the user to input a numeric value to filter the skill ratings. The filter is applied to column B (Skill Ratings).
6. Applying the Filter: The filter is applied using the AutoFilter method. It filters for values in column B that are greater than or equal to the value entered by the user.
7. Displaying Results: After applying the filter, a message box is shown, confirming that the filter has been applied.

How It Works in Practice

• When you run the CreateDynamicRangeAndFilter macro, it creates a dynamic range based on the data in columns A to C.
• It will then ask you for a minimum skill rating (e.g., a rating of 3 or higher).
• The data will be filtered based on the entered rating, and only the rows with ratings greater than or equal to the specified value will be visible.

Further Enhancements

• Additional Filters: You can extend this by adding multiple criteria (e.g., filtering by both skill ratings and feedback).
• Dynamic Charts: You can create charts that are based on the filtered data or the dynamic range.
• Conditional Formatting: You could add conditional formatting to highlight the top negotiation skills or performance levels.

This should give you a flexible and dynamic system for handling negotiation skills data within Excel using VBA. Let me know if you need further clarification or enhancements!