title: With VBA, how to sort data with different conditions?
body: <p>I would really appreciate some help to find a correct approach to solve my issue.</p>
  <p>I need to mach data from different sheets.</p>
  <p>In the sheet 1, I have this list of data.</p>
  <pre><code>Key Reference      COL B          COL C      COL D
  ID123                YZA              ...        ...
  ID123                BBA              ...        ... 
  ID123                XCP              ...        ... 
  ID123                ABC
  ID123                empty cell
  ID123                …
   
  ID124               empty cell
  ID124               XCP
  </code></pre>
  <p>…              …</p>
  <p>In the sheet2, I will only have the list of unique references</p>
  <pre><code>ID123
  ID124
  ID125
  </code></pre>
  <p>...</p>
  <p>By unique reference, I need to sort the data from the column B with the below conditions:</p>
  <ol>
  <li>empty cells</li>
  <li>string &quot;XCP&quot;</li>
  <li>all the rest (from ABC to YZA)</li>
  </ol>
  <p>Then, count the number of rows by unique reference
  Insert this number of rows in the sheet2
  and paste the data sorted.</p>
  <p>I think the easiest way to do it is to use a loop with a If statement for each of my condition instead of the sorting option.</p>
  <p>The expected result are : So it seems to be the same as sheet 1 but col b respect my sorting conditions</p>
  <pre><code>Key Reference        COL B          COL C      COL D
  ID123                empty cell       ...        ...
  ID123                XCP              ...        ... 
  ID123                ABC
  ID123                YZA
  ID123                …
   
  ID124               empty cell
  ID124               XCP
  </code></pre>
  <p>Please see below the code I try to create</p>
  <pre><code>Sub mapbreak5()
      Dim lr As Long, r As Long
      lr = Sheets(&quot;Sheet1&quot;).Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
      Dim rngKey As Range
      
      For r = 2 To lr
      If Sheets(&quot;Sheet1&quot;).Range(&quot;B&quot; &amp; r).Value = &quot;&quot; Then
      '...
      End If
      Next r
      'Or =&gt;
      
      Do
      If Range(&quot;B2&quot;) Is Empty Then
      Copy.EntireRow
          'find the respective key refence in the breaks sheet
          ThisWorkbook.Worksheets(&quot;breaks&quot;).Cells.Find(rngKey.Value, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
              'check if the IDxx field is already populated
              If Range(&quot;F2&quot;) Is Empty Then
              Range(&quot;E2&quot;).Paste.Selection
              Else: ActiveCell.Offset (1)
              Rows.Select
              Selection.Insert Shift:=xlDown
              End If
          Else: ActiveCell.Offset (1)
          End If
      Loop Until IsEmpty(ActiveCell.Offset(0, -1))
      
      Do
          If Range(&quot;B2&quot;) = &quot;XCP&quot; Then
          Copy.EntireRow
          'find the respective key refence in the breaks sheet
          ThisWorkbook.Worksheets(&quot;breaks&quot;).Cells.Find(rngKey.Value, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
              'check if the IDxx field is already populated
              If Range(&quot;F2&quot;) Is Empty Then
              Range(&quot;E2&quot;).Paste.Selection
              Else: ActiveCell.Offset (1)
              Rows.Select
              Selection.Insert Shift:=xlDown
              End If
          Else: ActiveCell.Offset (1)
          End If
      Loop Until IsEmpty(ActiveCell.Offset(0, -1))
  
      Do
          If Range(&quot;B2&quot;) Is Not Empty Or &quot;XCP&quot; Then
          Copy.EntireRow
          'find the respective key refence in the breaks sheet
          ThisWorkbook.Worksheets(&quot;breaks&quot;).Cells.Find(rngKey.Value, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
              'check if the IDxx field is already populated
              If Range(&quot;F2&quot;) Is Empty Then
              Range(&quot;E2&quot;).Paste.Selection
              Else: ActiveCell.Offset (1)
              Rows.Select
              Selection.Insert Shift:=xlDown
              End If
          Else: ActiveCell.Offset (1)
          End If
      Loop Until IsEmpty(ActiveCell.Offset(0, -1))
  
  End Sub
  </code></pre>