包括查找函数中的第一行

Question

我正试图在下面的代码中包括要排序的第一行("E1"(。我知道这必须是一件非常简单的事情，但我无法做到。有什么建议我该怎么做吗？

'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

Answer 1

查找方法提醒

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

Answer 2

您需要应用与设置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