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