title: Can't determine range rows count
body: <p>I have 3 comboboxes placed on a worksheet named cbo1, cbo2, and cbo3. When the user clicks an element from one, my code retrieves data from a database and places the results in a range of cells below the cbo.</p>
  
  <p>The Change event of each cbo sends its number (1, 2, or 3) to the routine below to identify which cbo that was clicked as well as its associated range (module level public variables). Those ranges are initialized in the Workbook_Open routine to an initial range of one cell, the first cell immediately below each cbo. That range will expand and shrink (varying rows, one column) depending on the results of the data retrieval.</p>
  
  <p>In the routine, I create a routine-level version of the chosen cbo and its range, do my work, then preserve the range size in the module-level range variables for the next time we come back into the routine. We need to know the range size from the last time so we can first clear it before putting new data into it.</p>
  
  <p>Here is my problem:<br/>
  I resize rngCBO (local variable) to hold arrResults. In doing so, the data is properly displayed in the appropriate cells. However, rngCBO.rows.count always reads as 1, no matter how many rows are actually in the range. (You can see I've placed a msgbox immediately after the resize to check it.) This presents a problem for the module-level variables rngCBO1, rngCBO2, and rngCBO3, because when we come <em>back</em> to this subroutine later, the first thing it is supposed to do is rngCBO.ClearContents. But since the rows always equals 1, only the first cell below the cbo's is getting cleared. Any cells below that which contain data are not getting cleared.</p>
  
  <pre><code>Option Explicit
  
  'Module variables
  Public rngCBO1 As Range
  Public rngCBO2 As Range
  Public rngCBO3 As Range
  
  Public Sub WBcboChange(intNum As Integer)
  
      Dim cboObj As ComboBox
      Dim rngCBO As Range
      Dim intR As Integer
      Dim intRows As Integer
      Dim strSQL As String
      Dim intFld As Integer
      Dim strFld As String
      Dim intChoice As Integer
      Dim rstResults As ADODB.Recordset
      Dim arrResults() As Date
  
      Select Case intNum  'Determine which cbo has been clicked (intNum brings it in)
          Case 1
              Set cboObj = wsStats.cboWB1
              Set rngCBO = rngCBO1
          Case 2
              Set cboObj = wsStats.cboWB2
              Set rngCBO = rngCBO2
          Case 3
              Set cboObj = wsStats.cboWB3
              Set rngCBO = rngCBO3
      End Select
  
      'clear any residual data from the cells under the cbo
      rngCBO.ClearContents
  
      If cboObj.Text &lt;&gt; "(none)" Then 'the user might just want to clear the cells and not be asking for more data
  
          intChoice = CInt(cboObj.Text)   'the cbo contains integer choices
  
      'Build the Fields string
          For intFld = 1 To 5  'there are five fields, each name being identical except ending in 1 through 5
              strFld = strFld &amp; "tblAllDAta.[fld" &amp; intFld &amp; "] = " &amp; intChoice
              If intWB &lt; 5 Then
                  strFld = strFld &amp; " OR "
              End If
          Next
  
          'the data being retrieved consists of dates
          strSQL = "SELECT tblAllDAta.[Date] " &amp; _
              "FROM tblAllDAta " &amp; _
              "WHERE " &amp; strFld &amp; _
              " ORDER BY tblAllDAta.[Date] DESC"
  
          OpenDB  'call the routine which opens the dB connection
              Set rstResults = GetReadOnlyRecords(strSQL) 'call the function that acquires the desired records
              intRows = rstResults.RecordCount
              If intRows &gt; 0 Then
  
                  'transfer data from the recordset into an array
                  ReDim arrResults(1 To intRows, 1 To 1)
                  intR = 1
                  rstResults.MoveFirst
                  Do While Not rstResults.EOF
                      arrResults(intR, 1) = rstResults("Date")
                      rstResults.MoveNext
                      intR = intR + 1
                  Loop
  
                  'THIS IS WHERE MY PROBLEM OCCURS (I think)
                  rngCBO.Resize(intRows, 1) = arrResults
                  MsgBox rngCBO.Rows.Count   'this is always 1 !!!!!!!!!!!!!!!!
  
              Else
                  'there were no records matching the query
                  rngCBO.Resize(1, 1) = "Never"
  
              End If
  
              Set rstResults = Nothing
          CloseDB
  
      End If
  
      'preserve the new ranges
      Select Case intNum
          Case 1
              Set rngCBO1 = rngCBO
          Case 2
              Set rngCBO2 = rngCBO
          Case 3
              Set rngCBO3 = rngCBO
      End Select
  
      Set rngCBO = Nothing
      Set cboObj = Nothing
  
  End Sub
  </code></pre>