We help IT Professionals succeed at work.

is there a better way of saying

Jagwarman
Jagwarman asked
on
67 Views
Last Modified: 2014-11-23
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
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Instead of 65536 use:

activesheet.rows.count

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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?
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
>> 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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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))
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.