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

For i = Range("E65536").End(xlUp).

so that I am not quoting numbers [65536 etc]

Thanks

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.

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?

SearchRange = Range("A1", Range("A65536").End(xlUp))

For i = Range("E65536").End(xlUp).

into rows count?

ASKER CERTIFIED SOLUTION

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.

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

For i = SearchRange To 1 Step -1

Note that this could be compressed to

For i = Activesheet.Usedrange.Rows

ASKER

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))

position = InStr(LCase(SearchRange(i,

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

SearchRange = Range("A1", Activesheet.usedrange.rows

For i = Activesheet.usedrange.rows

ASKER

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

Method 'Range' of object '_Global' failed

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

But thanks for trying

Open in new window