I am having real trouble trying to get the UsedRange to report the correct used range; here is my code that formats the sheet:

Public Sub FormatSheet(wkSht As Worksheet, rst As ADODB.Recordset, detailData As Boolean, Optional strCtlName As String, Optional strCtlValue As String)

Dim lngFieldCount As Long
Dim lngCounter As Long
Dim rng As Range
Dim cell As Range

    Set wkSheet = wkSht
    wkSheet.Activate
    ActiveWindow.FreezePanes = False
    
    With wkSheet
        .UsedRange.Validation.Delete
        .UsedRange.Delete
    End With
    
    Sheets("Detail").UsedRange
    
    lngFieldCount = rst.Fields.Count
    For lngCounter = 1 To lngFieldCount
        wkSheet.Cells(6, lngCounter).Value = rst.Fields(lngCounter - 1).Name
    Next lngCounter
    
    wkSheet.Range("A7").CopyFromRecordset rst
    
    lngColCount = wkSheet.UsedRange.Columns.Count
    lngFirstRow = getFirstRow(wkSheet.Name)
    lngLastRow = getLastRow(wkSheet.Name)
    
    Set rng = wkSheet.Range(wkSheet.Cells(lngFirstRow, 1), wkSheet.Cells(lngFirstRow, lngColCount))
    
    With rng
        .Font.Bold = True
        .Interior.ColorIndex = 15
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Borders.LineStyle = xlContinuous
    End With
    
    For Each cell In rng
        If IsDate("01/" & cell.Value & "/2009") Then
            cell.EntireColumn.NumberFormat = "#,###0"
        End If

        If cell.Value = "Total" Or cell.Value = "LY Total" Or cell.Value = "Variance %" Then
            cell.EntireColumn.NumberFormat = "#,###0"
        End If
    Next cell
    
    With wkSheet
        If detailData Then
            .Range("B1:G1,V1").EntireColumn.AutoFit
            .Range("A1").EntireColumn.Hidden = True
            With .Range("H:S")
                .ColumnWidth = 8.43
                .Locked = False
            End With
            .Range("H7").Select
            Sheets("Detail").UsedRange
        Else
            .Range("A1").EntireColumn.AutoFit
            .Range("B:M").ColumnWidth = 8.43
            .Range("B7").Select
            Sheets("Summary").UsedRange
            If strCtlName <> "" Then
                Dim o As OLEObject
                For Each o In wkSheet.OLEObjects
                    If o.Name = strCtlName Then
                        o.Object = strCtlValue
                    End If
                Next
            End If
        End If
    End With
    
    ActiveWindow.FreezePanes = True
    
End Sub
After the sheet is formatted the used range includes cells/rows that are well outside of the actual used range and everytime I run it the used range rows increases despite the actual data remaining the same size?! I've hardcoded the Sheets("Detail") to prove the process.