Remove long running loop with someting more efficient

**JP Romano** · 09-10-2009, 12:44 PM

Hi... I'm trying to make my spreadsheets run more efficiently, and the first goal I have is replacing as many loops as I can with smarter code. Since I'm neither a programmer, nor all that bright, I'm anticipating some challenges doing so. But since I have more than 20 spreadsheets, each of which can be up to 1 million lines, the loops just aren't cutting it and I'd like to do it better.

So, the question is, how can I replace something like the code below, which loops through column H and if the active cell contains the value "XXX", the value from 2 columns to the left (F) are copied over?

Please Login or Register  to view this content.

I've tried using filters, but I can't seem to get to ONLY the visible cells - so I kind of gave up on that approach.

Thanks for any guidance!

**royUK** · 09-10-2009, 12:57 PM

Try

Please Login or Register  to view this content.

**JP Romano** · 09-10-2009, 01:15 PM

Thank you Roy - but I'm not sure I fully understand the response. Sorry to be thick skulled, but won't that just set lastcl to equal the last cell in my range? I still have to loop through all the values to replace XXX, right?

Thanks again

**royUK** · 09-10-2009, 01:33 PM

Sorry misread the code

Please Login or Register  to view this content.

**JP Romano** · 09-10-2009, 01:40 PM

Wow... WOW...

Did I mention, WOW?

Thank you!

**royUK** · 09-10-2009, 01:42 PM

That worked then!

**JP Romano** · 09-10-2009, 02:20 PM

Sure did... cut down my sample from about 30 minutes to about 5 seconds.
So, of course, I took credit for it here at work. Getting a promotion and a raise, probably starting me off on a path to senior management, board of directors and ultimately, leisurely weekends on a yacht with (or without) the wife and kids.

I'm still amazed at how well that worked. Thank you so much!