Selecting Multiple rows in Excel using VBA

Question

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.

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.

The values will then be pasted into another sheet, which I have created along with the headers.

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.

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:

With wb.ActiveSheet


         'Call sortorder sub to sort Department field 
         SortOrder

         For i = 1 To lastcol
            'find department named column 
             If .Range(ConvertToLetter(i) & 1).Value = "department" Then      
              For j = 2 To MaxRowCount
                If .Range(ConvertToLetter(i) & j).Value = "Dept1" Then
                 'Get first cell address here to build range for 'dept1' data
                  firstRangeNumber = ConvertToLetter(i) & j
                  RHSCRange = firstRangeNumber & ":"

                ' 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

Answer 1

Solved this by using the find function, therefore no need to loop through rows and columns:

LastRow = Cells.Find("Dept1", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
firstrow = Cells.Find("Dept1", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

If you are going to use this make sure you sort your data first by the column you are searching - in this case the Department I am looking for is 'Dept1', so you can select the first and last row with 'Find', then select the whole range using the first and last row number (note: not the absolute first and last row number of data but of the range you are looking for):

Range = ConvertToLetter(1) & firstRow & ":" & ConvertToLetter(lastcol) & LastRow

Note: the Lastcol variable comes from a global variable set like this:

lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column

And the ConvertToLetter function is a piece of code you can find online to convert a number to a column character 1=A. I don't strictly need this now (replaced i with 1) as originally I was using a loop through the columns (For i = 1 To lastcol....next) to find the relevant column, and also was doing a loop with rows to look for the 'Dept1' value.

Then select, cut/copy and then paste range:

Range(Range).Select
Selection.Cut
'And then paste where you want it to go.