The 'Go To Special' Dialog in Excel 2010. Today, in Episode #1297, Bill looks at the 'Go To Special' Dialog as he shows us how to leverage the Special Cells property to quickly select all rows to hide or delete, using VBA in Microsoft Excel.
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Excel VBA, Chapter 12, GoTo 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 GoTo Special, GoTo Special.
All right! So, this actually came up in one of the seminars I was at, someone had a large data set 3900 rows.
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, OldWay look at it.
All right, it said, hey, we're going to go look at all the records figure out how many rows we have from loop, from two to final row for every single one look at column E, the fifth column.
If that value is true hide the row.
All right and we'll see how long it takes to run the OldWay here.
So, Alt+F8, click OldWay, click Run and what it's doing is this going through look at 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.
All right! So, that took 12 seconds the OldWay 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 into 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 is less than 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 one or some text called key and you'll see it really doesn't matter, what text we have there what I'm really interested in is those number ones.
All right! Now, we are going to use something that's cool here called GoTo Special.
Normally, that's found on the Home tab, come over here to Find and Select choose GoTo Special.
You can also use Control+G or F5, then click on Special and in GoTo Special if you've never used this dialog box, it's amazing we're going to ask for the Formulas that are evaluating two Numbers, not Text, not Logical, 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 NewWay, Edit that code.
All right! The macro starts out the same hey, figure out how many rows we have today, then we start from A1 resize down to the last row.
So, all this happens in one command, from that range E1 resized here's a continuation character.
We're going to use special cells and that property is equivalent to using GoTo Special.
We want the formulas and then the number one here interesting story about that number 1, I'll tell you in a minute and then .EntireRow.hidden is equal to 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 xlNumber out here.
All right and we talked about defined constants in an earlier podcast.
I think maybe chapter 2 was about to find constants and remember I said you and say 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 one.
So, the help file is not doing the right thing here.
So, actually I put in the number one instead of the defined constant.
So, just you know, earlier today I talked about in Chapter 2, how cool to define constants were, this one was completely just causing me a lot of headache until I figured out.
I actually turned on the Macro Recorder.
So, the Macro Recorder use the constant use the number one it's like okay, the guy who does the Macro Recorder knew was broken like who need tell the person who actually fixes the constant, but that's another story.
All right! So now, remember it took 12 seconds to do it the OldWay.
I'm going to try and run it the NewWay, we'll click Run, BAM in about one second and went through and was able to hide all of those rows very, very quickly some of you know, factor of 12 to 1.
The person who's taking minutes just imagine how much faster that was.
So, GoTo Special is a great dialog 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 for me a little bit so we were able to look for just the numeric cells we're able to take advantage of special cells and not have to loop through all records just come up with a very very quick...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Excel VBA, Chapter 12, GoTo 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 GoTo Special, GoTo Special.
All right! So, this actually came up in one of the seminars I was at, someone had a large data set 3900 rows.
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, OldWay look at it.
All right, it said, hey, we're going to go look at all the records figure out how many rows we have from loop, from two to final row for every single one look at column E, the fifth column.
If that value is true hide the row.
All right and we'll see how long it takes to run the OldWay here.
So, Alt+F8, click OldWay, click Run and what it's doing is this going through look at 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.
All right! So, that took 12 seconds the OldWay 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 into 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 is less than 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 one or some text called key and you'll see it really doesn't matter, what text we have there what I'm really interested in is those number ones.
All right! Now, we are going to use something that's cool here called GoTo Special.
Normally, that's found on the Home tab, come over here to Find and Select choose GoTo Special.
You can also use Control+G or F5, then click on Special and in GoTo Special if you've never used this dialog box, it's amazing we're going to ask for the Formulas that are evaluating two Numbers, not Text, not Logical, 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 NewWay, Edit that code.
All right! The macro starts out the same hey, figure out how many rows we have today, then we start from A1 resize down to the last row.
So, all this happens in one command, from that range E1 resized here's a continuation character.
We're going to use special cells and that property is equivalent to using GoTo Special.
We want the formulas and then the number one here interesting story about that number 1, I'll tell you in a minute and then .EntireRow.hidden is equal to 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 xlNumber out here.
All right and we talked about defined constants in an earlier podcast.
I think maybe chapter 2 was about to find constants and remember I said you and say 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 one.
So, the help file is not doing the right thing here.
So, actually I put in the number one instead of the defined constant.
So, just you know, earlier today I talked about in Chapter 2, how cool to define constants were, this one was completely just causing me a lot of headache until I figured out.
I actually turned on the Macro Recorder.
So, the Macro Recorder use the constant use the number one it's like okay, the guy who does the Macro Recorder knew was broken like who need tell the person who actually fixes the constant, but that's another story.
All right! So now, remember it took 12 seconds to do it the OldWay.
I'm going to try and run it the NewWay, we'll click Run, BAM in about one second and went through and was able to hide all of those rows very, very quickly some of you know, factor of 12 to 1.
The person who's taking minutes just imagine how much faster that was.
So, GoTo Special is a great dialog 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 for me a little bit so we were able to look for just the numeric cells we're able to take advantage of special cells and not have to loop through all records just come up with a very very quick...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
