(upbeat music)
- [Dennis] After seeing a few macros,
perhaps sensing the potential value to you,
let's actually create one.
And keep in mind too:
When you see a macro, don't always assume
that it's going to be valuable to you.
Ideally, a macro is based on the concept
that you find it valuable;
it's going to save you some time.
So what I'm suggesting here is the macro
you're about to see might not be valuable to you;
I know it has been for me.
I frequently want to know which cells have values in them:
numbers that are not formulas.
So how do we begin this process?
If we were doing this manually,
we'd click a single cell:
go to the Find and Select button on the Home tab,
choose Go To Special; you might not have seen that feature.
We'll click Constants.
And these boxes below,
although they look like they're for the Formulas button,
they also applied to Constants.
We want to see Constant Numbers
but not Text, Logicals, or Errors;
click OK, job is done.
Although, let's say, we'd like to highlight
these by adding a color,
we'll go to that Fill Color bucket;
this is the Font group on the Home tab.
Be sure to pick a light color
so we can see the text through this.
And then to clarify that this has actually been done,
just click on another cell
to unselect these but keep the color.
That's what we want our macro to do
but of course, instantly, rather than going through
these 12 steps or so.
So I'll undo the last action with Ctrl + Z
and just click a cell and start the process
by going to the View tab:
View tab off to the right,
click the arrow below Macros,
the drop arrow: Record Macro,
and the Record Macro dialog box pops up.
It gives us a temporary name,
but let's give it a real name,
and the name ideally will describe what the macro does:
Highlight, and we can't use spaces here,
but we can shift between upper and lower case
and/or use the underscore character
which I'm using here: Number Cells.
(keyboard typing)
And the macro name cannot begin with a number either.
Shortcut key: It's going to be handy at times, not always.
But in this case, it will be.
Our choices here are any of the lowercase
or any of the uppercase letters;
you want to be careful here.
If you were to use Ctrl + C and you probably wouldn't hear,
but if you did, you couldn't use Ctrl + C later for copy,
and many people use that.
Ctrl + N means create a new workbook, possibly use that.
So let's not use that.
I'm going to use by holding down
the Shift key pressing the letter N:
Ctrl + Shift + N.
Now, keystroke shortcuts are not required although handy.
After a while, if you've got lots of macros that use them,
you forget which one is which, and so on.
So I am using one here; it's going to make sense.
And for the moment, we'll store this macro in this workbook.
Description: What I'll do sometimes is just copy the name,
Ctrl + C, paste it down here;
maybe expand it a little bit by saying,
"in this worksheet" or "in the current worksheet."
(keyboard typing)
We're ready to record now.
And as we do, we want to stay focused
and not take actions that we don't want
to occur in the macro; click OK.
What do we do?
Make sure we click a single cell first:
I often do this on cell A1 just to emphasize that idea.
And then let's go to the Home tab,
and the manual steps we went through before:
Find and Select, Go To Special, choose Constants;
uncheck the boxes for Text, Logicals, and Errors;
click OK, Fill Color;
we use blue again, let's say.
And click and cell A1; we're done recording.
We go back to the View tab;
click the arrow under Macros, Stop Recording.
Let's test this out.
We'll jump over, for example, to the Profits sheet.
Click somewhere, Ctrl + Shift + N:
That highlights just the cells that have numbers;
no other cells in this worksheet have numbers.
How about that first worksheet called CurrentStaff:
Click anywhere, Ctrl + Shift + N;
we highlight just the cells with numbers,
and we see that off to the right too.
You cannot undo what a macro is done;
we can't press Ctrl + Z.
The undo arrow that we see up there has something
to do with what we did over on another worksheet,
but we can't undo what the macro does,
so you want to be a little bit careful;
stay away from recording macros that delete data
because then you won't be able to undelete.
Nevertheless, I think you can see the value here:
It's a simple, short macro but each time we use it,
it's one action instead of 10 or 12 actions.
I think you can see it's relatively easy to record a macro.
(upbeat music)