title: Sorting Range Addresses Numerically
body: <p>My code snippet is meant to look through all sheets in workbook and find the address of that specific text's location and copy the region back to a summary page.</p>
  <p><em><strong>Note: Code below does indeed work for my purposes.</strong></em></p>
  <p>The summary page algorithm is currently pasting the values as:</p>
  <blockquote>
  <p>2,3,1</p>
  </blockquote>
  <p>instead of the desired</p>
  <blockquote>
  <p>1,2,3</p>
  </blockquote>
  <p>Currently my FoundRange.Address =</p>
  <p><em>Pasted from Watch Window</em></p>
  <blockquote>
  <p>&quot;Watch :   :   FoundRange.Address : &quot;$B$60,$B$110,$B$34&quot; : String : Module1.Summarize_POL_Sheets</p>
  </blockquote>
  <p><em><strong>Note: <code>FoundRange</code> is a variable range that I created within this VBA code</strong></em></p>
  <p>In order for the get the desired paste order the addresses I would like to be change the address order to &quot;$B$34,$B$60,$B$110&quot;</p>
  <p>What would you all recommend would be the best coarse of action to remedy this?
  Is there a way to sort this range address by numerical value?</p>
  <p>Code snippet shown below:</p>
  <pre><code>    'Activate current sheet in the sheet loop
      sh.Activate
      
      'Returns a Range object that represents the used range on the specified worksheet
      Set FindRange = ActiveSheet.UsedRange
      'finds the last used cell within the active sheet
      Set LastCell = FindRange.Cells(FindRange.Cells.Count)
      'find specified value within our specified cell
      '   Set Find &quot;after&quot; operand to &quot;LastCell&quot;,i.e. the last cell used in the active sheet.
      '   The search will “wrap around”; this means it will go back to the start of the range.
      Set FoundCell = FindRange.Find(what:=ReqBlockString, after:=LastCell)
      
      'Test to see if anything was found
        'If FoundCell is something Then
        If Not FoundCell Is Nothing Then
          FirstFound = FoundCell.Address
        Else
          GoTo ReqBlockNotFound
        End If
      
      Set FoundRange = FoundCell
      
        'Loop until cycled through all unique finds
        'Do Loop (1)
        Do Until FoundCell Is Nothing
          'Find next cell with Text2Find value
           Set FoundCell = FindRange.FindNext(after:=FoundCell)
          
          'Add found cell to FoundRange range variable
           Set FoundRange = Union(FoundRange, FoundCell)
          
          'Test to see if cycled through to first found cell
           If FoundCell.Address = FirstFound Then Exit Do
            
        Loop
        'End Of Do Loop (1)
        
        '*************Add Sorting Here*******************
  
  
       'Copy test for FoundRange
       For Each PasteRange In FoundRange
       
      'FoundRange.Copy
      sh.Range(PasteRange.Address).CurrentRegion.Copy
       
      'Activate the Summary Sheet
      Sheets(&quot;Summary&quot;).Activate
       
      'Sets copy range to the next available empty row
      Set nextEmptyCell = Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Offset(1)
      nextEmptyCell.PasteSpecial (xlPasteColumnWidths)
      nextEmptyCell.PasteSpecial (xlPasteAll)
      
      Next PasteRange
  </code></pre>
  <p><strong>UPDATE (Corrected Order)</strong></p>
  <p>Moved the Union statement in-between the Exit condition and the Loop statement
  Updated Code shown here:</p>
  <pre><code>'Loop until cycled through all unique finds
        'Do Loop (1)
        Do Until FoundCell Is Nothing
          'Find next cell with Text2Find value
           Set FoundCell = FindRange.FindNext(after:=FoundCell)
          
          
          'Test to see if cycled through to first found cell
           If FoundCell.Address = FirstFound Then Exit Do
           
           'Add found cell to FoundRange range variable
           'Moving the Union statement here prevents the First cell from going 
           'through the union statement twice, this was the original culprit
           'for the improper ordering
           Set FoundRange = Union(FoundRange, FoundCell)
          
  
        Loop
        'End Of Do Loop (1)
  </code></pre>