title: How to use VBA dictionary to store discontinuous range
body: <p>I have some testing data of different samples, some samples may be test more than 1, and I want the last test data of each sample if exists. I tried to use an array to store data and return it to the worksheet, but it is not efficient enough. So, this time I want to use the VBA dictionary to store the data of each sample and then return it to the target position.</p>
  <p>If the range of each row is continuous, it is ok, I can use the sample number as key, and each row data of the sample as a value.</p>
  <p>However, in reality, I need to skip unwanted column, I tried to use union function, such as dic(sample) = Union ( .Range(…), .Range(…) ). Sadly, it did not work. I want to know how to connect discontinuous ranges and use this combined range as the value of the dictionary.</p>
  <p>Below is the VBA code, I can use a dictionary to store columns from B to E. Now I want to skip F, and store B to E, and G. I don't know how to modify the the code below to achieve my goal.</p>
  <pre><code>dic(sample) = .Range(.Cells(ii, 2), Cells(ii, 5))
  </code></pre>
  <hr>
  <pre><code>Sub DeleteDuplicate2()
  
  Dim tar_sheet As Worksheet, ii As Integer
  
  Dim dic As Object, arr As Variant
  
  Dim arr1, arr2, arr3, sample As String
  
  Set tar_sheet = ThisWorkbook.Sheets(&quot;data&quot;)
  
  Set dic = CreateObject(&quot;scripting.dictionary&quot;)
  
  tar_sheet.Activate
  
  With tar_sheet
  
      For ii = 3 To 7
      
          sample = .Cells(ii, 3).Value
          
          dic(sample) = .Range(.Cells(ii, 2), Cells(ii, 5))
      
      Next ii
      
      arr = dic.items
  
  End With
  
  'arr1 = arr
  'arr2 = Application.Transpose(arr)
  arr3 = Application.Transpose(Application.Transpose(arr))
  
  tar_sheet.Cells(10, 2).Resize(dic.Count, 4) = arr3
  
  Set dic = Nothing
  
  End Sub
  </code></pre>
  <p><img src="https://i.stack.imgur.com/bqQaO.jpg" alt="Wanted effect" /></p>