Selecting multiple rows in Excel using VBA
I am trying to process some rows in a sheet using VBA. I want to cut and paste a series of rows from one sheet to another and struggle to work out the code I need for the ID and select the last row for the ID of the entire range.
The rows I want are identified by the value in column A, it can be said that the value has the value "Dept1" in this cell, then I need to copy this row and any other row with that value in the cell.
Then the values will be pasted into another sheet that I created along with the titles.
My first attempt at doing this was to sort the data by column A and then loop through the data until I found the first cell with "Dept1" and then put that cell address in a variable, but I also need to specify the last 'dept1'. so I can get the line number for this.
The code I have so far is one that only puts the first cell address in a variable, but also needs the last cell address in order to then create the range that 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
source to share
Solved this with the find function so there is no need to iterate over 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 the data first by the column you are looking for - in this case, the Department I'm looking for is "Dept1", so you can select the first and last row with "Find ', then select the entire range using the first and last row number (note: not the absolute number of the first and last data row, but 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 on the internet to convert a number to a column character 1 = A. I don't need this now (I replaced it with 1) as I originally used a loop through the columns (for i = 1 for lastcol .... next) to find the corresponding column and also looped through the string to find the value "Dept1".
Then select, cut / copy and then paste the range:
Range(Range).Select Selection.Cut 'And then paste where you want it to go.
source to share