Find Method Help

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
Can someone assist in understanding the Find Method, in particular the following within lastrow

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

Thank You
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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...
Code:
[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...
Code:
[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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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
Back
Top