DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
Hi,
. Am I right in assuming that in the Range.Find-Methode, for either the optional argument xlNext or xlPrevious, the worksheet search goes through one complete sheet cycle? That is to say, that the search starts and stops at the same place.
. So for example with argument xlNext. – If the search starts somewhere in the middle of the sheet, then once it reaches the End of the sheet, it jumps back to the start and continues again in the forward direction with the search until it reaches the start point.
. Similarly for the case of the optional argument xlPrevious. - A search starting somewhere in the middle of the sheet would look backwards and when reaching the first cell, it jumps to the last cell and looks backwards until it reaches the start point.
. That ties up with my experimenting but I have not managed to find that stated anywhere yet.
. So a good way to find the Last cell with a value or formula in it would be as in the following code, kindly supplied to me by shg.
(Thisallows for different sheet sizes with different XL versions etc.)
Thanks
Alan Elston.
. Am I right in assuming that in the Range.Find-Methode, for either the optional argument xlNext or xlPrevious, the worksheet search goes through one complete sheet cycle? That is to say, that the search starts and stops at the same place.
. So for example with argument xlNext. – If the search starts somewhere in the middle of the sheet, then once it reaches the End of the sheet, it jumps back to the start and continues again in the forward direction with the search until it reaches the start point.
. Similarly for the case of the optional argument xlPrevious. - A search starting somewhere in the middle of the sheet would look backwards and when reaching the first cell, it jumps to the last cell and looks backwards until it reaches the start point.
. That ties up with my experimenting but I have not managed to find that stated anywhere yet.
. So a good way to find the Last cell with a value or formula in it would be as in the following code, kindly supplied to me by shg.
(Thisallows for different sheet sizes with different XL versions etc.)
Code:
Function Last(wks As Worksheet) As Range
Set Last = wks.Cells.Find(What:="*", _
After:=wks.Cells(1, 1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
End FunctionThanks
Alan Elston.
Last edited:
