Today, Elna sends in an interesting question: "While I know that "Ctrl+Down Arrow" jumps to the end of a Range, I want to know what the Shortcut Key is that will jump to the next Value that is different than the Current Cell. Is there such a Shortcut Key?" And, of course, MrExcel has a solution in Episode #1720: If the Find command in Excel supported Regular Expressions, this would be so much easier; unfortunately it currently does not. But, until that happens, a Small "Bill Jelen" VBA Macro, to find the next Cell with a different Value does the trick!
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1720 - Jump to Next Value!
Hey, great question sent in today by Elena. Elena has a cool idea, Ella is talking about Ctrl+Down arrow which is, you know, jumps to the end of a continuous range.
So Ctrl+Down arrow, Ctrl+Up arrow, Ctrl+Right arrow, It'll jump to the edge of a blank cell, Ctrl+Down arrow, or jump a gap, Ctrl+Down arrow, Ctrl+Down arrow, which is all cool, right?
But Ella says "wouldn't be cool if we had a way that we can be sitting here in the cell, and press a shortcut key and not have a jump to the end, but have it jump to the next cell that has a different value?" Well, I don't know of an Excel shortcut key that will do that, but it would be easy enough to write a little macro, Alt+F11.
And so when we run this macro, JumpToNextValue, we note the value of the active cell, also the row and the column.
And then, we +1 to the row, and check to see if we've surpassed the end of the spreadsheet.
So that little loop there is just kind of an error thing thrown in after the fact- let's hold off on that for a minute.
If Cells(ThisRow, ThisCol).Value = ThisVal , then we're going to go again.
Alright, and I know it's really bad to use GoTo in a program, but this is just a quick and dirty little program here.
So it goes back up, and +1 to the row, just going to keep going again through and through and through.
Otherwise, it will select the cell at this row, this column, and exit the subroutine.
So you know, for a small number of records, you know, 10-20-30-40, yeah, this would be a really, really fast, fast way to go.
Yeah, it would be faster we could use Ctrl or Ctrl+F, of we could use Find in VBA which we can, but right off the top I wasn't sure that'd be able to look for something that's not the current value.
You know, I can look for a specific thing, but I don't know what the next thing is going to be.
So, without the ability to, kind of modify Ctrl Find to say "I'm looking for something that's not ABC", just kind of stuck with this loop here.
I did consider the possibility that we actually hit the end of the spreadsheet, and that's what this code does.
If we've gotten past Application.Rows.Count, then it's going to Beep and select the last row in the spreadsheet.
Although practically, if you have a million, then it's going to loop here, it's going to take too long, it's not going to be an effective program.
Alright, so let's go back here and take a look, I'll press Alt+F8, and then click Run, and it jumps from ABC right down to D. Now we need a shortcut key for this, so let's go back to Alt+F8, and we'll click Options.
Let's see, Ctrl+D already does something, but Ctrl+Shift+D, so i type Shift+D in there, now Ctrl+Shift+D will run that macro.
Let's cancel and start up here again.
Alright, so we'll press Ctrl+Shift+D, jumps down DEF, Ctrl+Shift+D, Ctrl+Shift+D, Ctrl+Shift+D.
Now, one thing I didn't consider is what happens if it goes beyond the screen.
So we'll put ABC here, and then DEF here.
If we are at the top of this and press Ctrl+Shift+D, sure, it scrolls down, so it's kind of in the middle of the screen, Ctrl+Shift+D, It'll go to the blank cell.
Alright, there you go.
Alright great idea.
I want to thank Ella for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1720 - Jump to Next Value!
Hey, great question sent in today by Elena. Elena has a cool idea, Ella is talking about Ctrl+Down arrow which is, you know, jumps to the end of a continuous range.
So Ctrl+Down arrow, Ctrl+Up arrow, Ctrl+Right arrow, It'll jump to the edge of a blank cell, Ctrl+Down arrow, or jump a gap, Ctrl+Down arrow, Ctrl+Down arrow, which is all cool, right?
But Ella says "wouldn't be cool if we had a way that we can be sitting here in the cell, and press a shortcut key and not have a jump to the end, but have it jump to the next cell that has a different value?" Well, I don't know of an Excel shortcut key that will do that, but it would be easy enough to write a little macro, Alt+F11.
And so when we run this macro, JumpToNextValue, we note the value of the active cell, also the row and the column.
And then, we +1 to the row, and check to see if we've surpassed the end of the spreadsheet.
So that little loop there is just kind of an error thing thrown in after the fact- let's hold off on that for a minute.
If Cells(ThisRow, ThisCol).Value = ThisVal , then we're going to go again.
Alright, and I know it's really bad to use GoTo in a program, but this is just a quick and dirty little program here.
So it goes back up, and +1 to the row, just going to keep going again through and through and through.
Otherwise, it will select the cell at this row, this column, and exit the subroutine.
So you know, for a small number of records, you know, 10-20-30-40, yeah, this would be a really, really fast, fast way to go.
Yeah, it would be faster we could use Ctrl or Ctrl+F, of we could use Find in VBA which we can, but right off the top I wasn't sure that'd be able to look for something that's not the current value.
You know, I can look for a specific thing, but I don't know what the next thing is going to be.
So, without the ability to, kind of modify Ctrl Find to say "I'm looking for something that's not ABC", just kind of stuck with this loop here.
I did consider the possibility that we actually hit the end of the spreadsheet, and that's what this code does.
If we've gotten past Application.Rows.Count, then it's going to Beep and select the last row in the spreadsheet.
Although practically, if you have a million, then it's going to loop here, it's going to take too long, it's not going to be an effective program.
Alright, so let's go back here and take a look, I'll press Alt+F8, and then click Run, and it jumps from ABC right down to D. Now we need a shortcut key for this, so let's go back to Alt+F8, and we'll click Options.
Let's see, Ctrl+D already does something, but Ctrl+Shift+D, so i type Shift+D in there, now Ctrl+Shift+D will run that macro.
Let's cancel and start up here again.
Alright, so we'll press Ctrl+Shift+D, jumps down DEF, Ctrl+Shift+D, Ctrl+Shift+D, Ctrl+Shift+D.
Now, one thing I didn't consider is what happens if it goes beyond the screen.
So we'll put ABC here, and then DEF here.
If we are at the top of this and press Ctrl+Shift+D, sure, it scrolls down, so it's kind of in the middle of the screen, Ctrl+Shift+D, It'll go to the blank cell.
Alright, there you go.
Alright great idea.
I want to thank Ella for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
