Avoid a Loop - 1140 - Learn Excel from MrExcel Podcast

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 9, 2009.
A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Here is a great question that was sent in at the Power Analyst Boot Camp, on the VBA day.
Someone there had a worksheet, 750 rows of data in the worksheet, and up here they had some nice controls, where they could choose which regions to do and everything.
And we came down to a monster formula in column H that looked at all those controls, looked at the values over here, and chose whether or not we wanted to hide that row or keep that row.
Now I've dramatically simplified that calculation, I just looked at one thing, I didn't want to rebuild all the controls, because we're not talking about the controls here.
And then what they had was, they had a loop in VBA, it said For i = 10 To 750 so, from their headings down to the last row, check to see if the 8th column is equal to “Hide” and if it is, then we're going to hide that row.
And what was happening is, because we had things outside of that range that we're looking at just the visible rows, for example, maybe the Subtotal command, it was taking a while for that solution to run.
I mean, you can watch it here, in their particular case it was taking minutes for that whole solution to go through and selectively hide or unhide.
And we tried things like Application.ScreenUpdating = False, we tried turning off calculation, but it was still taking a reasonably long amount of time to go through and do that.
I said “OK, well, so you have hide and keep.” And I initially tried to AutoFilter, but unfortunately when you turn off the AutoFilter, then everything that you hid or undid, it doesn't work.
And so I said “We're going to edit that formula, and we're going to change the Keep portion to a number.” Alright, now why did I want to do that?
Why do I want to go with a number?
Here, let's stop this.
Alright, so we came back to our formula.
I said “We're going to edit that formula and change the Keep to a number, and copy that down.” And then I was interested in recording one tiny little bit of code, so I turn on the macro recorder, HowToShowText, and then use Go To, Ctr+G, Special, and we want only the Formulas that evaluate to Text, click OK.
Alright, we can stop recording, go look at that code and see what we have.
So we'll come here, the recorded code, of course, goes to Module2, SpecialCells(xlCellTypeFormulas, 2) is the way to get only the values that contain text!
Alright, so we'll come back to our faster way, and we'll paste that in, I'll say Range(“H10:H750).SpecialCells , and when we get those cells we want to do .EntireRow.Hidden = True . So it takes that big long loop that has to go through 740 different times, each time looking at a value, maybe deciding to hide or not hide.
And check this out, what it does, Alt+F8, choose FasterSolution, click Run and BAM, just like that, in one second it goes through and keeps just the values that we don't want to hide.
So, very cool there, instead of using a loop, we're able to change the formula just a bit.
And in fact, this column was hidden in the workbook, so no one even gets to see whether there's values there or whatever.
And then, by using SpecialCells, Go To Special, we were able to create a loop that runs much, much faster.
So I know, a lot of times, as I'm teaching people VBA, we talk about loops as the way to go, and that's fine if a loop is running fast.
But when we get to the point where the loop is actually taking a lot of time, then using SpecialCells here was a very clever way to solve that problem.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
 

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top