Create Dynamic Range Time Management with Excel VBA

This script will:

1. Define a dynamic range that adjusts based on the number of time entries in a specific column.
2. Sort time entries in ascending order.
3. Remove duplicate entries to ensure unique time values.
4. Format the time range properly for better readability.
5. Provide detailed explanations for each part of the code.

VBA Code: Dynamic Time Range Management

Option Explicit
Sub CreateDynamicTimeRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range, timeRange As Range
    Dim dict As Object
    Dim cell As Range
    Dim namedRange As String
    Dim timeColumn As String
    Dim startRow As Long   
    ' Set the worksheet where the time data is located
    Set ws = ThisWorkbook.Sheets("TimeData") ' Change sheet name as needed   
    ' Define the column where time values are stored
    timeColumn = "A" ' Change as needed
    startRow = 2 ' Assuming row 1 has headers   
    ' Find the last non-empty row in the time column
    lastRow = ws.Cells(ws.Rows.Count, timeColumn).End(xlUp).Row   
    ' Check if there are any data entries
    If lastRow < startRow Then
        MsgBox "No time data found!", vbExclamation, "Error"
        Exit Sub
    End If   
    ' Define the range containing time values
    Set rng = ws.Range(timeColumn & startRow & ":" & timeColumn & lastRow)   
    ' Sort the time column in ascending order
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange rng
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With   
    ' Remove duplicate time entries using Dictionary object
    Set dict = CreateObject("Scripting.Dictionary")   
    ' Loop through the range and store unique time values
    For Each cell In rng
        If Not dict.exists(cell.Value) And IsDate(cell.Value) Then
            dict.Add cell.Value, cell.Value
        End If
    Next cell   
    ' Clear any existing dynamic range before defining a new one
    namedRange = "DynamicTimeRange"
    On Error Resume Next
    ws.Names(namedRange).Delete
    On Error GoTo 0   
    ' Define a new dynamic range with unique sorted time values
    If dict.Count > 0 Then
        ' Write unique times back to a new column (e.g., Column B)
        ws.Range("B" & startRow & ":B" & lastRow).ClearContents
        ws.Range("B" & startRow).Resize(dict.Count, 1).Value = Application.Transpose(dict.items)       
        ' Define the dynamic range
        Set timeRange = ws.Range("B" & startRow & ":B" & (startRow + dict.Count - 1))
        ws.Names.Add Name:=namedRange, RefersTo:=timeRange       
        ' Format the new time range as Time
        timeRange.NumberFormat = "hh:mm AM/PM"       
        MsgBox "Dynamic Time Range created successfully!", vbInformation, "Success"
    Else
        MsgBox "No valid time data found.", vbExclamation, "Error"
    End If   
    ' Clean up objects
    Set rng = Nothing
    Set timeRange = Nothing
    Set dict = Nothing
End Sub

Detailed Explanation

1. Worksheet and Column Setup

Set ws = ThisWorkbook.Sheets(« TimeData ») ‘ Change sheet name as needed

timeColumn = « A » ‘ Change as needed

startRow = 2 ‘ Assuming row 1 has headers

• The macro operates on the sheet named « TimeData ».
• The time values are assumed to be in column A starting from row 2 (row 1 contains headers).

2. Finding the Last Row with Data

lastRow = ws.Cells(ws.Rows.Count, timeColumn).End(xlUp).Row

• This finds the last non-empty row in the selected time column.

3. Sorting Time Entries

ws.Sort.SortFields.Clear

ws.Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ws.Sort

    .SetRange rng

    .Header = xlNo

    .MatchCase = False

    .Orientation = xlTopToBottom

    .Apply

End With

• The macro sorts the time values in ascending order.

4. Removing Duplicates Using Dictionary

Set dict = CreateObject(« Scripting.Dictionary »)

For Each cell In rng

    If Not dict.exists(cell.Value) And IsDate(cell.Value) Then

        dict.Add cell.Value, cell.Value

    End If

Next cell

• A Dictionary object is used to ensure only unique time values are stored.

5. Defining the Dynamic Range

ws.Names(namedRange).Delete ‘ Clear existing range

If dict.Count > 0 Then

    ws.Range(« B » & startRow & « :B » & lastRow).ClearContents

    ws.Range(« B » & startRow).Resize(dict.Count, 1).Value = Application.Transpose(dict.items)

    Set timeRange = ws.Range(« B » & startRow & « :B » & (startRow + dict.Count – 1))

    ws.Names.Add Name:=namedRange, RefersTo:=timeRange

    timeRange.NumberFormat = « hh:mm AM/PM »

    MsgBox « Dynamic Time Range created successfully! », vbInformation, « Success »

End If

• The macro writes unique sorted times to column B.
• The dynamic range is then named « DynamicTimeRange ».

6. Handling Errors and Cleanup

On Error Resume Next

ws.Names(namedRange).Delete

On Error GoTo 0

• Ensures that an existing range is deleted before creating a new one.

How to Use This Macro

1. Place time values in Column A of the sheet named « TimeData ».
2. Run the macro CreateDynamicTimeRange.
3. The sorted, unique time values will be stored in Column B.
4. A named dynamic range « DynamicTimeRange » will be created.
5. The output will be formatted properly as time (hh:mm AM/PM).

Key Features

Handles dynamic data – Automatically adjusts when new time values are added.
Sorts time values – Ensures data is properly ordered.
Removes duplicates – Prevents redundant time entries.
Creates a named range – Makes it easy to reference in formulas or reports.
User-friendly messages – Provides alerts if issues arise.