Identifying Last Cell In Range

Jeffrey Smith

Well-known Member
Joined
Feb 11, 2005
Messages
795
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:
Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jeffrey Smith

Well-known Member
Joined
Feb 11, 2005
Messages
795
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
If you are dealing with a contiguous range,
Code:
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

Active Member
Joined
Apr 6, 2012
Messages
455
Maybe i misunderstood you, try this:
Code:
Sub test()
[c10:f20].Select
Set Rng = Selection
MsgBox Rng.Cells(Rng.Cells.Count).Address
End Sub
 

Jeffrey Smith

Well-known Member
Joined
Feb 11, 2005
Messages
795
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top