title: Selecting Multiple rows in Excel using VBA
body: <p>I am trying to process some rows in a sheet with VBA. I want to cut and paste a set of rows from one sheet to the other and struggling to work out the code I need to ID and select the last row to ID the whole range. </p>
  
  <p>The rows I want are Identified by the value in column A, lets say the value is 'Dept1' in that cell, then I need to copy that row and any other row with that value in the cell.</p>
  
  <p>The values will then be pasted into another sheet, which I have created along with the headers.  </p>
  
  <p>My first try at this involved sorting the data by the column A and then looping through the data until I found the first cell with 'Dept1' and then put this cell address into a variable, but I also need to ID the last 'dept1' value so I can get the row number for this.   </p>
  
  <p>The code I have so far is this, which only puts the first cell address in to variable but also need the last cell address to then create the range I want to select and cut:</p>
  
  <pre><code>With wb.ActiveSheet
  
  
           'Call sortorder sub to sort Department field 
           SortOrder
  
           For i = 1 To lastcol
              'find department named column 
               If .Range(ConvertToLetter(i) &amp; 1).Value = "department" Then      
                For j = 2 To MaxRowCount
                  If .Range(ConvertToLetter(i) &amp; j).Value = "Dept1" Then
                   'Get first cell address here to build range for 'dept1' data
                    firstRangeNumber = ConvertToLetter(i) &amp; j
                    RHSCRange = firstRangeNumber &amp; ":"
  
                  ' Create code to populate LastRangeNumber variable as explained below 
  
                  ' work out how to get last cell address number with 'dept1' 
                  ' and use lastcol variable value combined with last cell row number(j) 
                  ' to create last cell address for range. Finally combine first and 
                  ' last variables to create complete range, select and cut range to
                  ' New sheet
  
  
                  End If
                Next                         
               End If
           Next
       End With
  </code></pre>