# is there a better way of saying

SearchRange = Range("A1", Range("A65536").End(xlUp))
For i = Range("E65536").End(xlUp).Row To 1 Step -1

so that I am not quoting numbers [65536 etc]

Thanks
Phillip Burton

``activesheet.rows.count``
The problem with activesheet.rows.count is that for example in Excel 2010 it will give you a value of 1048576, so better would be Activesheet.usedrange.rows.count.

so how do I put this

SearchRange = Range("A1", Range("A65536").End(xlUp))
For i = Range("E65536").End(xlUp).Row To 1 Step -1

into rows count?
Phillip Burton

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> The problem with activesheet.rows.count is that for example in Excel 2010 it will give you a value of 1048576, so better would be Activesheet.usedrange.rows.count.

I assume that that is what Jagwarman wants - otherwise it's a very odd thing to have in Excel 2010.
Well my point is that with activesheet.rows.count his search range will include most likely over a million blank rows, while withActivesheet.usedrange.rows.count the search range will be limited to the actual number of used rows.
SearchRange = Activesheet.Usedrange.Rows.Count
For i = SearchRange To 1 Step -1

Note that this could be compressed to
For i = Activesheet.Usedrange.Rows.Count To 1 Step -1

not sure why Martin but I can't get yours to to run I get type mismatch at this point

position = InStr(LCase(SearchRange(i, 1)), LCase(targetString))
I'm sorry but I misread your original question and in my code SearchRange is a number (of rows) rather than a range, so my response to your question should have been.

SearchRange = Range("A1", Activesheet.usedrange.rows.count)
For i = Activesheet.usedrange.rows.count To 1 Step -1