Why would an Excel Worksheet object that represents a spreadsheet filled with data have an empty UsedRange field?

36 Views Asked by At

The purpose of the following Excel VBA code is to copy into an array of Variant the value of every cell contained within the used region of the spreadsheet.

Public Function GetSingletonSpreadsheetArray(spreadsheetName As String) As Variant
    Dim lastRow As Long
    Dim lastCol As Integer
    Dim ws As Worksheet
    
    If m_dictSpreadsheetArray Is Nothing Then
        Set m_dictSpreadsheetArray = New Dictionary
    End If
    
    If m_dictSpreadsheetArray.Exists(spreadsheetName) Then
        GetSingletonSpreadsheetArray = m_dictSpreadsheetArray(spreadsheetName)
        Exit Function
    End If
    
    Set ws = Worksheets(spreadsheetName)
    
    lastRow = ws.UsedRange.Rows.Count
    lastCol = ws.UsedRange.Columns.Count
    
    m_dictSpreadsheetArray.Add spreadsheetName, ws.Range(Cells(1, 1), Cells(lastRow, lastCol)).Value
    
    Set ws = Nothing
    GetSingletonSpreadsheetArray = m_dictSpreadsheetArray(spreadsheetName)
End Function

The Excel VBA debugger never returns attempting to single step through this line (from the above code):

    lastCol = ws.UsedRange.Rows.Count

Placing ws in Watch Window shows a mostly valid Worksheet object. Conspicuously missing is the UsedRange value:

enter image description here

Why does this Worksheet object have no UsedRange value? How do I get the last used row and column from this Worksheet object? The corresponding spreadsheet definitely has data in it... about 5,000 rows using columns A through T.

0

There are 0 best solutions below