In Excel, Page Down goes down one screen. Ctrl+Page Down goes to the next worksheet. How do you do Page Down to go right one screen full of columns? In today's episode, we look at Alt+Page Down, plus how to go right one screen in a macro. This is the first mention of the upcoming book, Excel Macro Recorder Success.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1916.
How to PageDown to the right?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today, I learned something this morning, I wanted to pass it on to you.
We're actually going to learn a couple of different tricks here today.
So we have a spreadsheet and if I press PgDn, I'm going to go to the next row.
So right now I'm seeing rows 1 through 21 and part of row 22.
So when I press PgDn, I'll go to that row, whatever the part of row 22.
So here I'm seeing 22.
Actually, let me resize it a little bit, so we can… so here I'm seeing 22 to 42, part of row 43, PgDn will take me to a row 43, alright.
Quick way to move down through a spreadsheet, of course PgUp goes backwards.
And I've known that trick.
The other related trick I knew, is if you have multiple worksheets, Ctrl+PgDn will move one sheet to the right.
And Ctrl+PgUp will move one sheet to the left, all right.
So I knew those two, but the question that came in this morning is: how do I do PgDn going to the right?
First of all, everyone has different size monitors and even if you have different size monitors, they might have, you know, resize the window down, so we don't know how many columns we're going to see.
How do we go to the right?
And Tab, no, Tab just goes one cell to the right.
Shift+Tab goes backwards, Alt+Tab goes to the next application, Ctrl+Tab goes to the next workbook.
There didn't seem to be any good way to do this.
Now, if you still have more data on the screen, you can come out here to the scroll bar and to the right of the scrollbar, so click in this area here, which will do something called a “large scroll”, alright.
So I'm seeing all of A, all of K, only part of L. When I do the large scroll I should move, so that way L, that part of column, most of the right.
See, but I can't keep doing that once I get to the end of the used range, alright.
So the answer: went out to the MrExcel message board, it is Alt+PgDn.
Alt+PgDn will move one screen full of data to the right, this should move this over to the column L, and now I can see all of V, but not quite W, so Alt+PgDn will move me to W and so on.
But so, that's a cool trick.
Picked one up today, that I had never used before.
Alt+PgDn to move to the right.
The actual question that came in was: someone has a very long macro and they need to be able to reliably move to the next screen to the right.
No matter what size screen the person has, resizes down to.
I said well, okay, let's see what, when we turn on the macro recorder, you know, and do the Alt+PgDn.
What does it record for us?
It's a Record Macro, HowToGoRight, store in the personal macro workbook.
I'm even going to assign it to Ctrl+Shift+R and we'll click OK.
All I do is one thing: Alt+PgDn.
And then down here: stop recording, all right.
Now I'll do F11, View, Project Explorer, I already have mine open, find your personal xlsb, find the last module, it might even be collapsed like this, you have to double click.
Find the last module and double click.
Okay, the macro record just frustrates me to no end.
I'm actually working on a new book project and I apologize, that I'm going to swear in the book title, but it makes me want to swear.
It's called “Macro Recorder Success: The crazy-*** stuff you have to do to get the macro recorder to work”.
So here the macro recorder did the large scroll for us: ActiveWindow.Largescroll ToRight :=1.
But then they hard coded, that is quite.., a column L. And so, this macro will only work when the window is this size.
If some other day the window is some other size and I run the macro, Ctrl+Shift+R, they do the large scroll to the right, but then it selects the wrong cell.
Ctrl+Shift+R, and eventually that's going to, yeah, right, it's always going to select column L. It's just, the macro recorder, just irritates me to no end.
So this, this has to go away and now we have the problem, that when I do Ctrl+Shift+R, Ctrl+Shift+R, it’s scrolling to the right, but the active cell isn't moving along with the scroll.
And when I press the Right arrow key, it's going to take me back to column B, because it never changed the active cell from where we started.
All right, so did a little bit more investigation today, I found some great new properties, that I've never used before.
ActiveWindow.VisibleRange tells us what we're seeing, right.
And you can actually ask for, you know, columns… or yeah, well, here, so I'm going to ask for Cells(1).Select, all right.
So from Cells(1), the first time actually, you know, said hey what's the address, right.
And then try to do a go-to, but it turns out, that this is just a great way to go.
So let's come back here to excel and we'll test it out.
We'll go Ctrl+Home, so we're starting in column A, I'm seeing all of A through H. When I press Ctrl+Shift+R, I'm hoping to end up in column I. Beautiful, Ctrl+Rhift+R, Ctrl+Shift+R.
The one thing you could do to make this better is, right now, let's say that we were down in row 11 and I did Ctrl+Shift+R, it's still going to go back up to column one.
Maybe we would want to remember where we were before we pressed Ctrl+Shift+R and go back down to that row.
But for right now, I'm going to go with that bit of code there.
Alright, so good chance that you learned a couple of new things today.
I think most people know PgDn, but Ctrl+PgDn to go to the next worksheet, Alt+PgDn, this one was completely new to me, I never figured out the shortcut key for that.
And then finally, here in VBA, to do the Alt+PgDn, large scroll to right and then there's a VisibleRange that tells me, you know, depending on what size screen we're having today, how, you know, what cells we’re going to actually see in the visible range.
All right, there you go, hey, I want to thank you for stopping by and we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1916.
How to PageDown to the right?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today, I learned something this morning, I wanted to pass it on to you.
We're actually going to learn a couple of different tricks here today.
So we have a spreadsheet and if I press PgDn, I'm going to go to the next row.
So right now I'm seeing rows 1 through 21 and part of row 22.
So when I press PgDn, I'll go to that row, whatever the part of row 22.
So here I'm seeing 22.
Actually, let me resize it a little bit, so we can… so here I'm seeing 22 to 42, part of row 43, PgDn will take me to a row 43, alright.
Quick way to move down through a spreadsheet, of course PgUp goes backwards.
And I've known that trick.
The other related trick I knew, is if you have multiple worksheets, Ctrl+PgDn will move one sheet to the right.
And Ctrl+PgUp will move one sheet to the left, all right.
So I knew those two, but the question that came in this morning is: how do I do PgDn going to the right?
First of all, everyone has different size monitors and even if you have different size monitors, they might have, you know, resize the window down, so we don't know how many columns we're going to see.
How do we go to the right?
And Tab, no, Tab just goes one cell to the right.
Shift+Tab goes backwards, Alt+Tab goes to the next application, Ctrl+Tab goes to the next workbook.
There didn't seem to be any good way to do this.
Now, if you still have more data on the screen, you can come out here to the scroll bar and to the right of the scrollbar, so click in this area here, which will do something called a “large scroll”, alright.
So I'm seeing all of A, all of K, only part of L. When I do the large scroll I should move, so that way L, that part of column, most of the right.
See, but I can't keep doing that once I get to the end of the used range, alright.
So the answer: went out to the MrExcel message board, it is Alt+PgDn.
Alt+PgDn will move one screen full of data to the right, this should move this over to the column L, and now I can see all of V, but not quite W, so Alt+PgDn will move me to W and so on.
But so, that's a cool trick.
Picked one up today, that I had never used before.
Alt+PgDn to move to the right.
The actual question that came in was: someone has a very long macro and they need to be able to reliably move to the next screen to the right.
No matter what size screen the person has, resizes down to.
I said well, okay, let's see what, when we turn on the macro recorder, you know, and do the Alt+PgDn.
What does it record for us?
It's a Record Macro, HowToGoRight, store in the personal macro workbook.
I'm even going to assign it to Ctrl+Shift+R and we'll click OK.
All I do is one thing: Alt+PgDn.
And then down here: stop recording, all right.
Now I'll do F11, View, Project Explorer, I already have mine open, find your personal xlsb, find the last module, it might even be collapsed like this, you have to double click.
Find the last module and double click.
Okay, the macro record just frustrates me to no end.
I'm actually working on a new book project and I apologize, that I'm going to swear in the book title, but it makes me want to swear.
It's called “Macro Recorder Success: The crazy-*** stuff you have to do to get the macro recorder to work”.
So here the macro recorder did the large scroll for us: ActiveWindow.Largescroll ToRight :=1.
But then they hard coded, that is quite.., a column L. And so, this macro will only work when the window is this size.
If some other day the window is some other size and I run the macro, Ctrl+Shift+R, they do the large scroll to the right, but then it selects the wrong cell.
Ctrl+Shift+R, and eventually that's going to, yeah, right, it's always going to select column L. It's just, the macro recorder, just irritates me to no end.
So this, this has to go away and now we have the problem, that when I do Ctrl+Shift+R, Ctrl+Shift+R, it’s scrolling to the right, but the active cell isn't moving along with the scroll.
And when I press the Right arrow key, it's going to take me back to column B, because it never changed the active cell from where we started.
All right, so did a little bit more investigation today, I found some great new properties, that I've never used before.
ActiveWindow.VisibleRange tells us what we're seeing, right.
And you can actually ask for, you know, columns… or yeah, well, here, so I'm going to ask for Cells(1).Select, all right.
So from Cells(1), the first time actually, you know, said hey what's the address, right.
And then try to do a go-to, but it turns out, that this is just a great way to go.
So let's come back here to excel and we'll test it out.
We'll go Ctrl+Home, so we're starting in column A, I'm seeing all of A through H. When I press Ctrl+Shift+R, I'm hoping to end up in column I. Beautiful, Ctrl+Rhift+R, Ctrl+Shift+R.
The one thing you could do to make this better is, right now, let's say that we were down in row 11 and I did Ctrl+Shift+R, it's still going to go back up to column one.
Maybe we would want to remember where we were before we pressed Ctrl+Shift+R and go back down to that row.
But for right now, I'm going to go with that bit of code there.
Alright, so good chance that you learned a couple of new things today.
I think most people know PgDn, but Ctrl+PgDn to go to the next worksheet, Alt+PgDn, this one was completely new to me, I never figured out the shortcut key for that.
And then finally, here in VBA, to do the Alt+PgDn, large scroll to right and then there's a VisibleRange that tells me, you know, depending on what size screen we're having today, how, you know, what cells we’re going to actually see in the visible range.
All right, there you go, hey, I want to thank you for stopping by and we'll see you next time for another netcast – MrExcel.
