0:00
hi everyone my name is Barbara welcome
0:01
back to my YouTube channel today I'm
0:03
going to show you how to do
0:05
this what I've just done is in one click
0:08
deleted all of the shapes in all of the
0:11
worksheets in this Excel workbook I had
0:13
to use this recently I had some text
0:15
boxes in multiple worksheets in multiple
0:18
workbooks and I had to delete only the
0:21
shapes in the workbook but I had to
0:22
leave the rest of it intact so that was
0:25
a good job I thought for a very small
0:28
piece of Visual Basic code or a macro so
0:33
I'm going to show you how to do that now
0:35
so what the code will do is one by one
0:37
delete all of the shapes on the
0:39
worksheet and then it will move to the
0:40
next worksheet in the workbook and do
0:42
the same thing until it's deleted all of
0:44
the shapes in the workbook so let's get
0:46
started first thing you want to do is
0:48
the developer go to the developer tab
0:49
and hit Visual Basic if you haven't
0:51
already got a module go insert a module
0:54
and then the next thing we do is declare
0:57
the start of a new sub routine or just
1:00
so uh we start that by typing sub which
1:03
is a sub routine and sub routine and
1:05
then we call it something so it's not
1:13
shapes and then Excel actually puts the
1:16
end sub or the end routine line in for
1:19
us which is always useful so the first
1:22
thing we want to do is declare a few
1:24
variables the first variable we want to
1:28
is the worksheets so I'll just say WS as
1:33
worksheet and then this will be used to
1:37
reference each of the worksheets in the
1:39
workbook the next one is the shapes so
1:46
ump as shape so now we've got two
1:51
variables one that uh will store or we
1:55
used to reference each of the worksheets
1:56
and then we've got one to store or
1:58
reference each of the shapes in the
2:00
worksheet that sounds good so the next
2:03
thing we want to do is we'll Loop
2:04
through each of the worksheets in the
2:07
workbook and loop through all of the
2:10
shapes in each of the worksheets so the
2:12
first thing we'll use the four for each
2:16
looping method uh for each worksheet
2:23
worksheets so now we're looking at each
2:26
of the worksheets in only the current
2:28
workbook with in there what we want to
2:30
do is Nest another small Loop to say and
2:42
worksheet shapes so that's the nested
2:45
Loop that will go through each of the
2:48
shap each of the shapes that are in each
2:49
of the worksheets so what do we want to
2:52
do with the shapes we actually want to
2:54
delete them so within this uh we can say
2:58
shape do delete Del so that will delete
3:03
shapes so that will delete the current
3:06
shape we then want to move it on to the
3:10
shape and then what we want to do is
3:14
move it on to the next
3:21
worksheet so now we have our Loop that
3:24
will go through each of the worksheets
3:26
within there we have the loop that goes
3:28
through each of the shapes in the
3:29
worksheet sheet and this will Loop until
3:32
all of the worksheet all of the shapes
3:34
in the worksheets have been deleted so
3:37
why don't we test it so let's come out
3:39
of there and then we can go to macros
3:42
and then I found my macro and let's run
3:47
it and they've gone so with this very
3:51
small but powerful piece of code we've
3:54
looped through all of the worksheets and
3:56
it doesn't matter how many worksheets
3:57
you've got in your workbook or how many
3:58
shapes it'll through and it will delete
4:01
them all for you with a click of a
4:02
button I'll leave the code in the box
4:06
below if you want to use it feel free to
4:09
use it and I do hope you enjoyed the
4:11
video and got something out of it if you
4:13
did leave a comment give it a thumbs up
4:15
or subscribe really does help the
4:16
channel if you've got any questions
4:17
leave them in the comment box below I
4:19
read all comments and answer your
4:21
questions I'll see you next time for
4:22
another XL video thanks for watching
