Link to home
Start Free TrialLog in
Avatar of Jagwarman
Jagwarman

asked on

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
Avatar of Phillip Burton
Phillip Burton

Instead of 65536 use:

activesheet.rows.count

Open in new window

Avatar of Martin Liss
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.
Avatar of Jagwarman

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> 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
Martin I am now getting
Method 'Range' of object '_Global' failed

Phillip Burton's works for me so I am going to accept his solution

But thanks for trying