Identifying Last Cell In Range

[Jeffrey Smith]

Ok, so I thought I'd seen this before but I can't seem to come across it in the forums.

I've created my own search routine. Once the first value has been found, I want to find the next value that meets the criteria without having to search all the previous cells. The end user highlights a range of cells and starts the user form. Instead of having to manually find the last cell in the highlighted range, isn't there a way to identify the last cell in the range?

Something like: Set LastCell = Rng.last


This code finds the next cell to search and the last cell in the highlighted range and saves it to SearchRng so the GoFindIt code can use it:

Private Sub FindNext_Click()
  
  Dim Cell As Range
  Dim Rng As Range
  Dim Test As Integer
  Dim NextCell As Range
  
  Set Rng = Selection
  
  For Each Cell In Rng
    If Test = 0 And Cell.Address = ActiveCell.Address Then
      Test = 1
    ElseIf Test = 1 Then
      Set NextCell = Cell
      Test = -1
    End If
    Set LastCell = Cell
  Next Cell
  
  Set SearchRng = Range(NextCell, LastCell)
  Call GoFindIt
End Sub

 

[andrewkard]

Hello, maybe so:
Set LastCell = Rng.Cells(Rng.Cells.Count)

 

[Jeffrey Smith]

Nope, counting the cells doesn't help me find the address of the last cell in the selection. I need to reference the new range to be searched that doesn't include the previous cells searched.

 

[mikerickson]

If you are dealing with a contiguous range,

Set LastCell = myRange.Item(myRange.Cells.Count)

I'm not sure if it works with a discontinuous range and am not in a place where I can test.

As far as finding things in a range, have you looked at the .Find method?

 

[andrewkard]

Maybe i misunderstood you, try this:

Sub test()
[c10:f20].Select
Set Rng = Selection
MsgBox Rng.Cells(Rng.Cells.Count).Address
End Sub

 

[Jeffrey Smith]

Andrew,
Ok, I made a wrong assumption on your first post. I thought the code you posted only counted the number of cells in the selection. It actually does retrieve the last cell in the selection, even if it is multiple columns.

Mike,
It doesn't work as well on non-contiguous ranges. I could be wrong because I haven't tested it, but I think .Find is just like the using the standard find in Excel and it won't let me use qualifiers such as < > = or => or <=.

Jeff