Reputation: 48590
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
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
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
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
Reputation: 48590
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
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