►
From YouTube: Excel Office Scripts: Range basics
Description
Checkout this page for code samples used:
https://github.com/sumurthy/officescripts-projects/edit/main/Range%20Basics
A
Hello
in
this
video,
I'm
going
to
walk
through
office
scripts
range
object
range
is
really
the
center
of
the
excels
of
a
scripts
object
model,
and
this
is
a
great
place
to
begin.
If
you're
new
to
office
scripts
range
offers
a
whole
varieties
of
apis
to
help
classify
them
into
simple
categories.
I've
created
this
document.
On
the
left
hand,
side
you
can
find
the
link
to
this
in
the
video
description
arrange
is
anything
that
a
single
cell
represents
within
excel
or
a
multiple
cells,
so
both
are
referred
to
as
range
now.
A
The
apis
that
you
have
can
be
kind
of
thought
about
in
these
categories.
One
is
set
of
methods
that
return
some
metadata
associated
with
the
range
like
its
address,
a
number
of
cells
or
rows
or
column
counts,
and
then
the
next
is
methods
that
return
some
form
of
data
associated
with
the
range.
For
instance.
In
here
I
have
a
set
of
values
or
texts
that
the
user
sees.
A
A
And
then
you
have
set
up
methods
that
returns.
Other
range
object
associated
with
the
range
object
you're
dealing
with,
for
instance,
you
may
want
to
get
a
next
cell
or
the
entire
column,
or
a
used
range
and
so
on.
So
these
are
very
useful
when
you
don't
know
exactly
what
type
of
data
you're
dealing
with,
so
you
may
want
to
navigate
around
and
locate
the
right
cell
or
range
to
either
read
or
set
values.
A
So
it
becomes
very
important
that
you,
you
can
navigate
around
using
one
range
and
just
jump
to
another
range,
and
there
are
some
most
of
these
methods
are
off
of
a
range,
but
there
are
also
a
couple
where
workbook
offers
the
selected
range
and
active
cells.
So
these
are
pretty
much
the
only
two
methods
where
you
get
that
off
of
the
workbook
and
then
there
are
methods
that
return
a
range
object
in
relation
to
another
range
object.
A
For
instance,
you
can
say
hey
give
me
intersection
of
two
ranges
and
you'll
get
another
range
back
with
which
represents
the
intersection
of
those
two
and
then
range
also
offers
other
methods
that
gives
you
related
objects
like
the
worksheet
that
you're
part
of
or
if
there's
a
table,
a
pivot
table
associated
with
that
range.
It
gives
that
collection
of
those
tables
of
pivots,
back
and
style
associated
with
the
range
or
data
validation
linked
to
our
range
and
so
on.
A
A
Now
I'm
going
to
just
showcase
some
of
them
just
to
give
you
a
flavor
of
what
these
apis
look
like
to
begin
with.
How
do
you
get
a
reference
to
a
range,
so
the
easiest
way
is
to
create
off
of
the
let's
say
on
the
worksheet
you're
on:
let's
say
you
can
get
a
range
object
using
the
get
range
api.
So
here
you
can
use
the
range
address
notation
and
since
you're
already
in
the
worksheet,
you
don't
need
to
prefix
the
worksheet
name.
A
So
let's
say
I
want
to
get
a1
through
e5
here
and
the
the
metadata
returning
apis
that
I
talked
about.
We'll
use
one
of
them
range
get
address,
so
I
should
you
know,
get
back
the
same
a1
e5
that
I
used
right
and
now
to
use
some
of
these
singular
and
two-dimensional
or
returning
constants
associated
with
range.
So
let's
say
I
want
to
get
range
and
get
one
of
the
cells.
So
let's
say
I
want
to
get-
I
don't
know,
maybe
the
last
row
and
then
the
product
column.
A
A
So,
that's
that's
that
now
you
will
notice
that
there
is
also
something
called
get
value
now
for
the
most
part
there
it's
the
same
as
the
text,
but
in
some
cases
like
say
in
this
case,
where
it's
a
percentage
and
the
value
and
then
the
text
of
the
user
sees
are
different.
So
let
me
change
the
the
cell
to
get
the
last
rating
here.
You
will
see
that
the
value
is
probably
a
0.92,
whereas
if
I
get
its
text
value,
it's
going
to
be
what
you
actually
see
which
is
92.
A
So
you
need
to
be
just
aware
of
what
type
of
value
that
you're
dealing
with
and
then
and
also
the
number
format
associated
with
it.
So
in
this
case
like
if
I
get
the
number
format
of
that
cell
you'll
see
that
it's
it's
a
percentage
format
so,
depending
on
the
percentage
format,
sorry,
the
number
format
the
value
in
a
cell
might
be
different
than
what
the
user
sees
that
sees.
That
cell.
As
so
that's
that's
the
singular
methods
and
then
for
the
two-dimensional
methods.
A
Now
on
the
range
you
can
also
use
a
singular
version,
but
it
always
defaults
to
the
top
left
corner.
So
here,
when
I
do
this,
I
get
the
year
column
right
all
right.
So
that's
just
a
flavor
of
the
singular
and
the
plural
data
retrieving
apis
to
so
we
already
looked
at
the
get
cell,
which
returns
the
range
object
representing
a
single
cell.
A
Now
you
can
also
use
some
of
the
other
ones
say,
for
instance,
let's
say
I
want
to
get
get
cell,
let's
say
2
0
or
rather
0
0
and
I
can
get
the
entire
column
and
get
its
address
so
here.
Obviously
this
is
in
the
entire
column,
a
so
you'll
get
the
entire
column
name.
But
then
I
can
say:
hey
give
me
just
the
used
range,
so
you'll
see
that
I
can
chain.
A
A
A
A
You
know
one
common
scenario
that
users
come
across
is
to
remove
filters
associated
with
it
on
a
given
column,
where
the
active
cell
is
present.
So
to
do
that,
you
know
first
I'll
start
out
with
getting
the
cell,
so
my
goal
is
to
remove
you
know
clear
the
filter
on
this
category
column.
Again,
I
don't
know
if
it
is
part
of
a
table
or
not
so
I
don't
know
I
in
the
active
cell
could
be
somewhere
outside
the
table.
A
Get
get
me
the
active
cell.
Okay.
So
now
that
I
have
the
cell,
I
can
try
to
figure
out
if
it
is
part
of
a
table.
So
I
can
say:
hey
give
me
all
the
tables
source
associated
with
this
cell.
So
it's
a
get
table,
so
it's
plural.
So
that
means
any
you
know.
Range
can
be,
can
span
across
many
different
cells
and
many
different
drains.
So
hence
it
returns
all
the
tables.
So
in
this
case
I
know
I'm
I'm
only
interested
in
in
a
single
table.
A
If
it's
not
within
a
table.
I
just
you
know
I
don't
want
to
go
further,
so
I
can
say
hey
if
the
table's
length
is
not
equal
to
one.
So
I
can
say
you
know
this
is
not
cell
is
not
part
of
our
table.
So
if
the
active
cell
is
elsewhere,
it's
gonna
just
end
the
script,
because
there
is
nothing
to
do
further,
whereas
if
it's
inside
a
table
you
know
I
can
continue.
Okay,
so
I
do
wanna
end
the
script
if
there
is
nothing
to
process.
A
A
A
So
I
I
can
do
that
by
using
a
method
called
get
intersection
on
the
range,
so
I
can
get
the
entire
column
of
the
cell
and
I
can
get
the
intersection
of
that
along
with
the
entire
table
range,
and
that
should
only
give
me
the
entire
column
using
which
I
can
then
then
navigate
to
the
table.
Table
column
object.
So
to
do
that,
let's
say
you
know,
create
a
variable
called
intersection,
and
what
I'm
interested
in
is
on
the
cell.
Object,
give
me
the
entire
column
and
then
get
me.
A
The
intersection
with
the
table
range
object.
So
this
should
get
me
just
the
the
column
that
I'm
interested
in.
So
here
I'm
just
going
to
again
print
the
address
to
make
sure
I'm
on
the
right
track,
so
I
should
get
b1
through
b15
and
this
will
work
even
if
I
have
rows
around
it.
So
let's
say
I
have
some
rows
here
and
columns
here.
A
A
A
All
right
so,
once
I
have
the
text,
I
can
go
back
to
the
table
and
get
me
get
the
column
by
name
it's
a
header
name.
So
this
gives
the
the
actual
column
and
then
off
of
that
I
can
get
the
filter
object
and
then
just
clear
it
so
clear.
The
method
off
of
the
table
filter
now
the
reason
I
have
to
do
all
this
is
because
the
range
itself
doesn't
have
that
or
I
may
not
be
able
to
get
to
the
correct
filter
object
off
of
the
range.
A
A
Obviously
nothing
will
happen
and
now,
if
I'm
inside
of
a
table
but
not
in
a
column
where
there
is
no
filter
on
it,
and
then
nothing
really
will
happen,
because
there
is
nothing
to
clear.
But
if
I'm
on
a
column,
I
see
that
the
active
cell
is
in
a
column
inside
a
table
that
has
a
filter.
It
will
clear
that
filter
on
the
column,
so
you'll
see
that
the
table
expanded
because
it
cleared
all
the
filter.
A
So
that's
just
a
flavor
of
what
you
can
do
by
linking
range
with
other
objects
and
chaining
methods
and
getting
to
the
the
underlying
object,
you're
interested
in
and
executing
methods,
whether
to
retrieve
or
to
write
back
or
you
know,
use
methods
like
clear
in
this
case.
So
hopefully
that's
given
you
a
little
bit
of
information
about
the
range
and
where
to
get
started
out
with.
If
you
have
more
scenarios
in
mind,
please
do
leave
a
comment.