Find Method Help

[billandrew]

Can someone assist in understanding the Find Method, in particular the following within lastrow

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Thank You

 

[Rick Rothstein]

Can someone assist in understanding the Find Method, in particular the following within lastrow

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

First off, I don't like the code line you posted as it omits specifying some argument that the Find function retains from use to use (whether that use is via code or the Find/Replace dialog box). This is more robust...

[table="width: 500"]
[tr]
	[td]LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues, SearchFormat:=False).Row[/td]
[/tr]
[/table]

This can be coded more compactly by omitting the named arguments and using consecutive commas for non-specified values...

[table="width: 500"]
[tr]
	[td]LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row[/td]
[/tr]
[/table]

As to how it works... The "*" says to find any cell containing a value (specified by the LookIn argument). The search is to be "upward" (specified by the SearchDirection argument). The search starts, by default, from the top left cell and wraps from top to bottom of the range (because the After argument is omitted). The "upward" search will occur across each row before moving up to the next row (specified by the SearchOrder argument). The False in the last argument says to ignore any search format settings that may be set from a previous use of the Find function. There are other arguments that do not carry over from use to use, and have been omitted. You can get the full argument list and an explanation of each by visiting the help page for this function. Simply place the text cursor within or next to the keyword Find in a legitimate code line containing it (or type Range.Find in the Immediate Window and place the text cursor in or adjacent to the keyword Find) and press the F1 key.

 

[billandrew]

Thank you very much Mr. Rothstein as always....