Learn how to leverage the Special Cells property to quickly select all rows to hide or delete.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Excel VBA Chapter 12 - Go To Special Hey, welcome back to the MrExcel netcast, working through the VBA book here.
Chapter 12 is all about filters and in this particular case, I want to talk about Go To Special, Go To Special all right.
So, this actually came up in one of the seminars I was at, someone had a large data set 3,900 rows and they had a little formula over here, saying which rows they wanted to hide and which rows they didn't want to hide.
Okay. So, they, they wrote a simple little macro.
Let's go, take a look at the macro, they had, Developer, Macros, Old way. Click it, all right.
It's, hey, we're going to go look at all of our work experience, how many rows we have, from loop, from 2 to Final Row, for every single one look at column E, the fifth column, if that value is true, hide the row, alright, and we'll see how long it takes to run the OldWay here.
So, Column D, Click OldWay, Click Run and what it's doing is, it's going to through, looking every single row.
It's even recalculating this subtotal after every row is hidden so, a lot of calculations happening here, repainting the screen again and again and again, alright.
So, that took 12 seconds the Old Way.
And for whatever reason, in that case, it actually had a lot more form as it was taking like minutes to go through and run this and I said, "All right, let's see if there's a better way to go." I'm going to go on to another worksheet here, where all of the data on the left, is the same.
But I took their formula, they had a formula here that said, "If D4 < 0.5" and I rewrote that formula, I said, Okay, if it's less than 0.5 then, I don't want to put true or false.
I want to put something that we can differentiate. I want to put either the number 1 or some text, Call it 'Keep' and you'll see, it really doesn't matter what text we have there, what I'm really interested in, is those number 1.
All right, I want to do something that's cool here, called GoTo Special.
Normally that's found on the Home tab .
Come over here to Find and Select. Choose Go To Special. You can also use Ctrl+G or F5.
Then, click on Special and in Go To Special, you've never used this dialog box, it's amazing.
We're going to ask for the formulas that are evaluating to Numbers, not Text, Not Logicals, not Errors, click OK and you'll see that what happens then is, they select only the numeric cells.
All right, now we're going to take advantage of this in VBA.
We'll take a look at the New Way. So, Edit that code. All right, the macro starts out the same.
Hey, figure out any rows we have today.
Then, we start from E1, resize down to the last rows. So, all this happens in one command, from that range E1 rersized. Here's a continu... a continuation character.
We're going to use Special Cells and that property is equivalent to using Go To Special.
We want the formulas and then the number 1 here.
Interesting story about that number 1 I'll tell you in a minute and then .Entire Row.Hidden=True So, in one fell swoop, it's going to find all the numbers and hide those rows in a single command. All right, now.
Excel help says that this is supposed to be Excel number out here, all right and we talked about Defined Constants in an earlier podcast, I think, maybe Chapter two was about Defined Constants and remember I said, you take, in order to take print xlright and you can see what that value is. Well for some reason print xlnumber.
It's not set up correctly. It's supposed to be the number 1, so the help file is not doing the right thing here.
So, I actually, have to put in the number 1 instead of the defined constants.
So just, you know, earlier today, I talked about in Chapter two, how cool the defined constants work and this one was completely, just causing me a lot of headache, until I figured out.
I actually turned on the Macro Record. So, the macro recording use the constant, use the number 1, I said, okay.
The guy who does the macro recording, it was broken like, why he couldn't tell the person who actually fixes the constant but that's another story, all right.
So now, number took 12 seconds to do it the Old Way.
I'm going to try and run it, the New Way, will click Run.
BAM! in about one second and went through and was able to hide all of those rows very, very quickly.
So, you know a factor of 12 to 1, for the person who's taking minutes, just imagine, how much faster that was.
So, Go To Special is a great dialogue box in Excel, very hidden, most people never see it.
The equivalent function in VBA is Special Cells.
Here, this was a situation where by tweaking that formula a bit, so we were able to look for just in the numeric cells. We're able to take advantage of Special Cells and not have to loop through all records.
Just came up with a very, very quick thing.
All right, thank you for stopping by.
We'll see you next time for another netcast, for MrExcel.
Excel VBA Chapter 12 - Go To Special Hey, welcome back to the MrExcel netcast, working through the VBA book here.
Chapter 12 is all about filters and in this particular case, I want to talk about Go To Special, Go To Special all right.
So, this actually came up in one of the seminars I was at, someone had a large data set 3,900 rows and they had a little formula over here, saying which rows they wanted to hide and which rows they didn't want to hide.
Okay. So, they, they wrote a simple little macro.
Let's go, take a look at the macro, they had, Developer, Macros, Old way. Click it, all right.
It's, hey, we're going to go look at all of our work experience, how many rows we have, from loop, from 2 to Final Row, for every single one look at column E, the fifth column, if that value is true, hide the row, alright, and we'll see how long it takes to run the OldWay here.
So, Column D, Click OldWay, Click Run and what it's doing is, it's going to through, looking every single row.
It's even recalculating this subtotal after every row is hidden so, a lot of calculations happening here, repainting the screen again and again and again, alright.
So, that took 12 seconds the Old Way.
And for whatever reason, in that case, it actually had a lot more form as it was taking like minutes to go through and run this and I said, "All right, let's see if there's a better way to go." I'm going to go on to another worksheet here, where all of the data on the left, is the same.
But I took their formula, they had a formula here that said, "If D4 < 0.5" and I rewrote that formula, I said, Okay, if it's less than 0.5 then, I don't want to put true or false.
I want to put something that we can differentiate. I want to put either the number 1 or some text, Call it 'Keep' and you'll see, it really doesn't matter what text we have there, what I'm really interested in, is those number 1.
All right, I want to do something that's cool here, called GoTo Special.
Normally that's found on the Home tab .
Come over here to Find and Select. Choose Go To Special. You can also use Ctrl+G or F5.
Then, click on Special and in Go To Special, you've never used this dialog box, it's amazing.
We're going to ask for the formulas that are evaluating to Numbers, not Text, Not Logicals, not Errors, click OK and you'll see that what happens then is, they select only the numeric cells.
All right, now we're going to take advantage of this in VBA.
We'll take a look at the New Way. So, Edit that code. All right, the macro starts out the same.
Hey, figure out any rows we have today.
Then, we start from E1, resize down to the last rows. So, all this happens in one command, from that range E1 rersized. Here's a continu... a continuation character.
We're going to use Special Cells and that property is equivalent to using Go To Special.
We want the formulas and then the number 1 here.
Interesting story about that number 1 I'll tell you in a minute and then .Entire Row.Hidden=True So, in one fell swoop, it's going to find all the numbers and hide those rows in a single command. All right, now.
Excel help says that this is supposed to be Excel number out here, all right and we talked about Defined Constants in an earlier podcast, I think, maybe Chapter two was about Defined Constants and remember I said, you take, in order to take print xlright and you can see what that value is. Well for some reason print xlnumber.
It's not set up correctly. It's supposed to be the number 1, so the help file is not doing the right thing here.
So, I actually, have to put in the number 1 instead of the defined constants.
So just, you know, earlier today, I talked about in Chapter two, how cool the defined constants work and this one was completely, just causing me a lot of headache, until I figured out.
I actually turned on the Macro Record. So, the macro recording use the constant, use the number 1, I said, okay.
The guy who does the macro recording, it was broken like, why he couldn't tell the person who actually fixes the constant but that's another story, all right.
So now, number took 12 seconds to do it the Old Way.
I'm going to try and run it, the New Way, will click Run.
BAM! in about one second and went through and was able to hide all of those rows very, very quickly.
So, you know a factor of 12 to 1, for the person who's taking minutes, just imagine, how much faster that was.
So, Go To Special is a great dialogue box in Excel, very hidden, most people never see it.
The equivalent function in VBA is Special Cells.
Here, this was a situation where by tweaking that formula a bit, so we were able to look for just in the numeric cells. We're able to take advantage of Special Cells and not have to loop through all records.
Just came up with a very, very quick thing.
All right, thank you for stopping by.
We'll see you next time for another netcast, for MrExcel.
