Find Last Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have many entries describing sales events, and one of the columns in every
entry is quarter. I'd like to find where one quarter starts and where one
ends without doing any filtering or deleting.
For example, i have

___K___
1|Q1
2|Q1
3|Q1
4|Q1
5|Q1
6|Q2
7|Q2

I'd like to use a code that, in this example, would return row 5 as the last
Q1 value. Is this possible?
thanks in advance

comparini3000
 
Simple enough

=MAX(IF(A1:A20="Q1",ROW(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

To get the first row, change MAX to MIN

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Give this a try...

Public Sub FindLast()
Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = Range("K2", Cells(Rows.Count, "K").End(xlUp))
Set rngFound = rngToSearch.Find(What:="Q1", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchDirection:=xlPrevious)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
MsgBox "Found on row " & rngFound.Row
End If

End Sub
 
Mr. Phillips, thank you for your quick reply, the formula worked wonderfully.
However, I was looking for a VBA solution. How would I do that?
 
MsgBox ActiveSheet.Evaluate("MAX(IF(A1:A20=""Q1"",ROW(A1:A20)))")



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top