Including first row in find function

user_16360988 0
Viewed 63

I´m trying to include the first row ("E1) to be sorted as well in the following code. I know that it has to be something really simple, but I cannot manage it. Any suggestion how can I do it?

'Sort value=1
'Find first row with value=1 in column E
With ws
  FstRowSub = .Range("E:E").Find(What:=1, After:=.Range("E1")).Row
  'Find last row with value=1 in column E
  LastRowSub = .Range("E:E").Find(What:=1, After:=.Range("E1"), searchdirection:=xlPrevious).Row
  'Set range which includes rows with value=5 in column E
  Set rngSub = .Range(Cells(FstRowSub, "A"), Cells(LastRowSub, LastColumn))
            
'Sort the range based on the data in column B (value=1)
rngSub.Sort _
   key1:=.Range("B1"), _
   order1:=xlAscending, _
   Header:=xlNo
vba excel
2 Answers

You need to apply the same kind of logic used when you set LastRowSub

Dim rng As Range, FstRowSub As Long

Set rng = Sheet1.Range("E:E")

'This starts looking *after* the first cell...
FstRowSub = rng.Find(What:=1, After:=rng.Cells(1)).Row

Debug.Print FstRowSub    ' >>> 2

'If you want to find the first matching value in rng then
'   start *after* the last cell in the range and it will loop
'   back and start in the first cell
FstRowSub = rng.Find(What:=1, After:=rng.Cells(rng.Cells.Count), _
                  searchdirection:=xlNext).Row

Debug.Print FstRowSub   ' >>> 1
user_4788840

A Find Method Reminder

With ws
    
    ' The Find method will fail if the worksheet is filtered.
    If .AutoFilterMode Then .AutoFilterMode = False
    
    ' It is safer to search for a cell and then test if it was found.
    ' The LookIn and LookAt parameter have no default value and are saved
    ' each time the Find method is called.
    ' The LookIn parameter 'xlFormulas' allows a correct search even
    ' when there are hidden cells (rows or columns) (not filtered)
    ' and can succesfully be used since the formula and the value
    ' of the number 1 are the same ('1').
    ' The LookAt parameter 'xlWhole' ensures that the whole cells will be
    ' checked for the What parameter, not parts of them, i.e. 12 is not a match.
    
    ' To start the search from the first cell of the range (in this case 'E1'),
    ' you need to use the last cell of the range as the After parameter:
    ' Either '.Range("E" & .Rows.Count)' or '.Range("E" & .Cells.Count)'
    ' or '.Cells(.Rows.Count, "E")' or '.Cells(.Cells.Count, "E")'.
    ' Think: After the last cell comes the first cell.
    ' The SearchDirection parameter is 'xlNext' by default and can be omitted.
    Dim fCell As Range: Set fCell = .Columns("E").Find(What:=1, _
        After:=.Range("E" & .Rows.Count), LookIn:=xlFormulas, LookAt:=xlWhole)
    If fCell Is Nothing Then Exit Sub ' value not found
     
    ' The After parameter is by default the range's first cell
    ' (in this case 'E1') and can therefore be omitted.
    ' Think: Before (After + xlPrevious) the first cell comes the last cell.
    Dim lCell As Range: Set lCell = .Columns("E").Find(What:=1, _
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlPrevious)
    ' No need to check if the cell was found since we're looking for the same
    ' value and it was previously found.
   
    Dim rngSub As Range: Set rngSub = .Range(fCell, lCell)
    
    ' You forgot the dots for the cells in your code (not needed here):
    'Set rngSub = .Range(.Cells(FstRowSub, "A"), .Cells(.LastRowSub, LastColumn))
            
    rngSub.Sort _
        Key1:=.Range("B1"), _
        Order1:=xlAscending, _
        Header:=xlNo

End With
user_98140690
Related