Today, in Episode #1448, Bill re-addresses a few past podcasts with alternate solutions.
1. A Non-VBA Solution to get Misplaced Excel Windows back into view (improving upon Episode #1437).
2. A VBA Solution to Center Across Selection (as discussed in Episode #1443).
3. A Mouse Click Solution to move to the Edge of your Data quickly.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
1. A Non-VBA Solution to get Misplaced Excel Windows back into view (improving upon Episode #1437).
2. A VBA Solution to Center Across Selection (as discussed in Episode #1443).
3. A Mouse Click Solution to move to the Edge of your Data quickly.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1448 - Mailbag.
Welcome back to the MrExcel netcast. I'm Bill Jelen.
I want to catch up on a few things over the last couple weeks.
Back in Episode 1437, we talked about my friends down the street who had two different people using the spreadsheet – one with a big monitor, one with a small monitor.
The guy was the small monitor would open up and couldn't see the spreadsheet.
You can see that there was something going on there, and I wrote a little macro for him with the move around.
What happened was, it was actually way down below and I run a macro Bowman and MD said, “Hey, no.
You don't have to do that.
Just come here to view, arrange all, and that will bring those things that are too far down below back into view.” And then last week, the Center Across Selection, wow, generated a lot of email about Center Across Selection.
I lamented the fact that merge and center is, you know, right here in the ribbon but the Center Across Selection, you can't find it.
You have to go in to format cells and once you get into Format Cells, Alignment, Horizontal, Center Across Selection.
Someone said, “Well, wait a second.
Hey, this is easy.
It's a one-line macro.” And he's right.
It is a one-line macro.
So let's just...
I'm here in my personal macro workbook.
I'll create a quick little Sub CenterAcross().
We just say, Selection.horizontalalignment=xlCenterAcrossSelection, like that.
So we now have a macro called center across.
We go back to Excel.
We want to add that to our quick access toolbar.
So go into Macros, Center Across, Add, and then down here, click the modify button.
I realize that's out of view of your screen.
Try and find something.
It looks like center.
Well, I’m not seeing anything that looks like center.
I'm going to use this little compass point.
I don't know.
Who knows?
And tooltip, Center Across Selection.
Click OK.
Click OK.
So now, we choose that title in the four cells, center across, and we get the Center Across Selection.
Remember, the advantage of that is, as we go up past that cell, it does not expand the selection to all four columns, which merge would do.
Now one last one, this was sent in by Jim.
Jim said...
You know, Center Across Selection used to be an icon a long time ago back around Excel 95.
It was just kind of wiped away by the sands of time.
Then he said, “You know, the other one, I used to think that there was a way that I could shoot down to the edge of the adjacent data, like clicking the fill handle, but I don't want to fill.
I just want to move down there.” And I said, “Yes, there is.” This is pretty cool.
You stay away from the fill handle but go to that bottom edge and double-click.
You see it shoots right down there to row 221.
I can double-click the left edge, double-click the top edge, and double-click the right edge to quickly move to the edge of the data without filling.
Now of course, that's the same as Ctrl down arrow, Ctrl up arrow, Ctrl left arrow, Ctrl right arrow.
Yes, that's the keyboard but if you're a mouse person, then this is a good way to go.
It even scrolls it up into view, which is a little bit better than the Ctrl down arrow.
I hate to say that because I’m a keyboard person.
There you have it.
All right.
So hey, thanks for letting me catch up on several different issues today.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1448 - Mailbag.
Welcome back to the MrExcel netcast. I'm Bill Jelen.
I want to catch up on a few things over the last couple weeks.
Back in Episode 1437, we talked about my friends down the street who had two different people using the spreadsheet – one with a big monitor, one with a small monitor.
The guy was the small monitor would open up and couldn't see the spreadsheet.
You can see that there was something going on there, and I wrote a little macro for him with the move around.
What happened was, it was actually way down below and I run a macro Bowman and MD said, “Hey, no.
You don't have to do that.
Just come here to view, arrange all, and that will bring those things that are too far down below back into view.” And then last week, the Center Across Selection, wow, generated a lot of email about Center Across Selection.
I lamented the fact that merge and center is, you know, right here in the ribbon but the Center Across Selection, you can't find it.
You have to go in to format cells and once you get into Format Cells, Alignment, Horizontal, Center Across Selection.
Someone said, “Well, wait a second.
Hey, this is easy.
It's a one-line macro.” And he's right.
It is a one-line macro.
So let's just...
I'm here in my personal macro workbook.
I'll create a quick little Sub CenterAcross().
We just say, Selection.horizontalalignment=xlCenterAcrossSelection, like that.
So we now have a macro called center across.
We go back to Excel.
We want to add that to our quick access toolbar.
So go into Macros, Center Across, Add, and then down here, click the modify button.
I realize that's out of view of your screen.
Try and find something.
It looks like center.
Well, I’m not seeing anything that looks like center.
I'm going to use this little compass point.
I don't know.
Who knows?
And tooltip, Center Across Selection.
Click OK.
Click OK.
So now, we choose that title in the four cells, center across, and we get the Center Across Selection.
Remember, the advantage of that is, as we go up past that cell, it does not expand the selection to all four columns, which merge would do.
Now one last one, this was sent in by Jim.
Jim said...
You know, Center Across Selection used to be an icon a long time ago back around Excel 95.
It was just kind of wiped away by the sands of time.
Then he said, “You know, the other one, I used to think that there was a way that I could shoot down to the edge of the adjacent data, like clicking the fill handle, but I don't want to fill.
I just want to move down there.” And I said, “Yes, there is.” This is pretty cool.
You stay away from the fill handle but go to that bottom edge and double-click.
You see it shoots right down there to row 221.
I can double-click the left edge, double-click the top edge, and double-click the right edge to quickly move to the edge of the data without filling.
Now of course, that's the same as Ctrl down arrow, Ctrl up arrow, Ctrl left arrow, Ctrl right arrow.
Yes, that's the keyboard but if you're a mouse person, then this is a good way to go.
It even scrolls it up into view, which is a little bit better than the Ctrl down arrow.
I hate to say that because I’m a keyboard person.
There you have it.
All right.
So hey, thanks for letting me catch up on several different issues today.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
