CODEWITHSUNDEEP
vbaexcel
Nikhil Agrawal
Nikhil Agrawal

Reputation: 48600

Check if cell is the last cell of Excel Range

I am looping over MyRange range. I need to find out if Cell is the last cell of MyRange.

For Each Cell In MyRange.Cells        
    If Cell = ' What should I write here?
        'Do some stuff
    End If
Next Cell

I've tried this:

If Cell = MyRange.Cells(0, MyRange.Count) Then

But it gives error.

How shoud I go about it?

Upvotes: 1

Views: 2369

Answers (5)

Hubisan
Hubisan

Reputation: 1172

To check if 2 ranges are the same you can use the intersect method (if they don't intersect they are not the same).

Sub lastCellInForEach()
    Dim cell As Range, myrange As Range

    Set myrange = ActiveSheet.Range("A1:C1000")

    For Each cell In myrange.Cells
        'using Intersect to check if cell is the last cell in the range
        If Not Intersect(cell, myrange(myrange.cells.count)) Is Nothing Then
            debug.print cell.address
        End If
    Next cell

End Sub

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

I like some of the other answers but shortest way could be as follow:

If Cell.Address = myRange.Cells(myRange.Cells.Count).Address Then

Important note This above solution will work if myRange is continuous range of cells.

If your area is not continuous use a logic of @Doug Glancy which in line could be presented as follow:

If Cell.Address = myRange.Areas(myRange.Areas.Count).Cells(myRange.Areas(myRange.Areas.Count).Cells.Count).Address Then

Please! if somebody want to reward this answer please do it automatically with @Doug Glancy answer, too (who was first).

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27488

Your answer works fine, but it's an interesting question. Here's a way to figure it out beforehand:

Sub test()
Dim MyRange As Excel.Range
Dim cell As Excel.Range
Dim LastCell As Excel.Range

Set MyRange = Selection
Set LastCell = MyRange.Areas(MyRange.Areas.Count).Cells(MyRange.Areas(MyRange.Areas.Count).Cells.Count)
For Each cell In MyRange
If cell.Address = LastCell.Address Then
    MsgBox cell.Address
    Exit For
End If
Next cell
End Sub

Note that in both our methods, if there's more than one area, the "last cell" may not be the bottom-most or rightmost cell. For example select cells J10:J20 and then E5:E10 and run the above. The result will be E10, since it was selected last.

Upvotes: 5

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48600

I did this to reach conclusion

Dim i As Integer: i = 1

For Each Cell In MyRange.Cells            

    If i = MyRange.Cells.Count Then
        'Do if stuff using Cell
    Else
        'Do else stuff using Cell
    End If       

    i = i + 1

Next Cell

Upvotes: 2

RAS
RAS

Reputation: 3395

AFAIK, you can access Cells by index.
try replacing For Each loop with for loop.

I think something like this should work(untested): 

Dim rng As Integer = myrange.Cells.Count
For i As Integer = 0 To rng - 1
            '...do something
    If i = rng Then
    End If
Next

Upvotes: 1

Related Questions