Hey, welcome back to the MrExcel netcast, I’m Bill Jelen. Here is a great question that was sent in at the Power LS Boot Camp on the VBA day. Someone there had a worksheet 750 rows of data in 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 looks at all those controls looked at values over here and chose whether not we went to the hide that row or keep that row.
Now, I’ve dramatically simplified that calculation. I just look 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 eight column is = to hide and if it is then we’re going to hide that row.
What was happening is because we had things outside to that range that we’re looking at just the visible rows for example, maybe the sub-total and command. It was taking a while for that solution to run. I mean, you can watch here. In that particular case it was taking minutes for that whole solution to go through and selectively hide or unhide and we try things like application to that screen updating equal false. We tried turning off calculations, but it was still taking a reasonably long amount of time to go through and do that.
I said, okay well, you see have hide and keep and I initially tried to AutoFilter, but unfortunately, when you turn off the AutoFilter then everything you hid or unhid doesn’t work and so I said, “We’re going to edit that formula and we’re going to change the key portion to a number.”
Alright, now why would I want to do that? Why I want to go with a number? I’ll stop this alright, so we came back to our formula and we’re going to edit that formula and change to keep to a number and copy that down, and then I was interested in recording one tiny little bit of code, so I turned on the Macro recorder, how to show text and then use go to control 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 come here at the record code of course goes to module two, special cells (Excel Cell Type Formulas, 2) is the way to get only the values that contain text.
Alright, so I’ll come back to our faster way and I’ll paste that in. I’ll say a RangeH10:H750.special cells, and when we get those cells we want to do .entire row .hidden is equal to true, so it takes that big long loop that has go to through 740 different times. Each time looking at a value maybe this time it will hide or not hide and check this out, what it does Alt F8, choose faster solution, click run and just like that.
In one second it goes through and it keeps just the values that we don’t want to hide, so a very cool there instead of using a loop, we are able to change the formula just a bit and in fact this column was hidden in her workbook, no one even gets to see whether there’s values there or whatever, and then by using special cells, go to special, we are 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 for loop is running fast when we get to the point where the loop is actually taking a lot of time, and then using special cells here was a very clever way to solve the problem.
I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
Transcription by:
Scribe4you Transcription Services