1 00:00:00,610 --> 00:00:06,060 Hello, guys, and welcome back to our requests of our course about the complete introduction to Vehbi 2 00:00:06,070 --> 00:00:07,450 with Microsoft Excel. 3 00:00:08,740 --> 00:00:15,310 So in today's class, we are still going to learn some tips and tricks that you guys can use basically 4 00:00:15,310 --> 00:00:18,480 to be able to program with VBA. 5 00:00:19,810 --> 00:00:24,120 So you'll see learn some pretty cool things, in my opinion. 6 00:00:24,340 --> 00:00:25,670 What's left to learn? 7 00:00:25,690 --> 00:00:29,680 It's not that complicated, but it can help you out. 8 00:00:30,100 --> 00:00:35,790 So what I want to teach you today is basically how you can clear some cells. 9 00:00:35,800 --> 00:00:39,830 So let's say, for example, you have I don't know if you have things inside of a cell. 10 00:00:40,270 --> 00:00:47,230 So here, I'll just write down a text and I'll write down text on all those cells. 11 00:00:47,560 --> 00:00:50,260 And basically, I want to clear some of those cells. 12 00:00:51,220 --> 00:00:56,130 And to be able to do this, I will need to use Imakura. 13 00:00:56,140 --> 00:01:02,620 Well, I can do it with the use of the delete button, but I prefer writing the only macro that will 14 00:01:03,190 --> 00:01:05,980 basically delete one part of the text. 15 00:01:06,250 --> 00:01:08,280 So that's exactly what we're going to do today. 16 00:01:08,740 --> 00:01:11,680 We're going to learn how to delete things. 17 00:01:12,070 --> 00:01:17,170 Also, we are going to learn how to basically change the name of the sheets that you guys have right 18 00:01:17,170 --> 00:01:17,400 there. 19 00:01:17,740 --> 00:01:20,160 And this is like a little bonus in my opinion. 20 00:01:20,170 --> 00:01:21,450 This could be something really cool. 21 00:01:22,060 --> 00:01:26,890 Let's say, for example, we receive a report each and every day that contains around 20 sheets. 22 00:01:27,280 --> 00:01:31,000 And you need to rename them the same way each and every day. 23 00:01:32,230 --> 00:01:36,800 Well, basically, you can use a macro instead of changing their names each and every day. 24 00:01:37,420 --> 00:01:38,430 So let's start. 25 00:01:38,680 --> 00:01:39,020 All right. 26 00:01:39,040 --> 00:01:42,190 So first of all, what I'm asking you to do is pretty simple. 27 00:01:42,670 --> 00:01:45,460 Just create yourself a little thing like this. 28 00:01:45,790 --> 00:01:50,740 And after that, we are going to go inside of our visual basics. 29 00:01:51,760 --> 00:01:57,430 So when we're on our visual basics, like always what we're going to do, we are going to create a new 30 00:01:57,430 --> 00:02:02,820 module for our micro, as always, simply go in, insert into insert a new module. 31 00:02:03,280 --> 00:02:07,840 Once it's done, we should have a blank page and from now on, what want to do. 32 00:02:07,870 --> 00:02:13,210 So as always, we're going to insert a procedure because we are going to write down eight macros. 33 00:02:13,210 --> 00:02:15,170 So we need to use a sub procedure. 34 00:02:15,640 --> 00:02:18,460 So we're going to create a procedure and we're going to call it. 35 00:02:18,760 --> 00:02:19,820 So we'll give it a name. 36 00:02:20,560 --> 00:02:21,550 Let's name it. 37 00:02:22,300 --> 00:02:23,560 So let's make it clear. 38 00:02:26,410 --> 00:02:33,370 So right now we have our procedure and now let's say we want to basically delete some elements that 39 00:02:33,370 --> 00:02:34,100 are right there. 40 00:02:34,390 --> 00:02:37,630 So what I want to do, I don't want to delete all the cells. 41 00:02:37,840 --> 00:02:42,910 I really want to I really want to select cell by cell the cells that I want to delete. 42 00:02:43,800 --> 00:02:46,770 And this is basically exactly what we are going to do to it. 43 00:02:47,340 --> 00:02:51,000 So pretty simple how exactly do delete some of the cells. 44 00:02:51,030 --> 00:02:54,790 So honestly, it's just a simple line of code. 45 00:02:55,530 --> 00:02:59,180 So the first thing we are going to select the range that we want to delete. 46 00:02:59,610 --> 00:03:01,770 So we write down our range function. 47 00:03:02,760 --> 00:03:06,220 And here we're going to write down what we want to delete. 48 00:03:06,660 --> 00:03:10,200 So in this case, let's say we want to delete the five. 49 00:03:12,060 --> 00:03:13,710 So this cell right there. 50 00:03:20,290 --> 00:03:24,700 And basically, the only thing that we need to right after that is simply clear. 51 00:03:27,190 --> 00:03:28,040 So here we go. 52 00:03:29,140 --> 00:03:34,880 And now if we are up, what's going to happen is that, as you can see, the five have been deleted. 53 00:03:35,740 --> 00:03:37,810 So what we can do as well is pretty simple. 54 00:03:38,470 --> 00:03:43,320 We can basically write down all the cells that we want to delete and simply make a list of them. 55 00:03:44,320 --> 00:03:47,680 And as you can see, it's going to be pretty simple to do so. 56 00:03:47,680 --> 00:03:49,150 It's one of the five. 57 00:03:50,390 --> 00:03:53,360 You want to delete E seven? 58 00:03:54,940 --> 00:03:57,340 We want to delete EF three. 59 00:03:58,450 --> 00:04:06,660 And they want to delete, I don't know, age six and we want to delete, let's say I know. 60 00:04:08,410 --> 00:04:10,170 So what we'll do right now is pretty simple. 61 00:04:10,180 --> 00:04:11,850 We're just going to run everything. 62 00:04:12,310 --> 00:04:17,080 And as you can see, we have deleted all the cells that we wanted to ask. 63 00:04:17,100 --> 00:04:21,850 Can see it's a really simple line of code, but it can be really useful in the case that you guys need 64 00:04:21,850 --> 00:04:23,310 to delete some elements. 65 00:04:23,310 --> 00:04:29,520 So it's not usually you don't use the clear the clear bottom because you can just click on delete. 66 00:04:30,130 --> 00:04:34,750 But sometimes, let's say, for example, instead of repeating the same task again, again, again and 67 00:04:34,750 --> 00:04:37,720 again on a lot well, on huge folders. 68 00:04:38,680 --> 00:04:43,300 Let's say, for example, you know, that you want to delete some cells in particular each and every 69 00:04:43,300 --> 00:04:43,470 day. 70 00:04:44,110 --> 00:04:47,560 Well, you can create yourself a simple map to be able to do so. 71 00:04:48,400 --> 00:04:49,060 All right, great. 72 00:04:49,900 --> 00:04:52,610 So this is for the first microbe that we are going to learn today. 73 00:04:53,320 --> 00:04:57,940 So the first thing the second thing that I want to teach you guys to do is actually pretty simple. 74 00:04:58,180 --> 00:04:59,830 And once again, this is a bonus. 75 00:04:59,830 --> 00:05:02,920 So I thought about it a little bit earlier today. 76 00:05:03,220 --> 00:05:11,050 And basically, in my opinion, it's important to know how to change the name of your sheets. 77 00:05:11,380 --> 00:05:16,630 So I teach at the beginning of the class how you can save your document with the use of macros, how 78 00:05:16,630 --> 00:05:17,410 you can create. 79 00:05:17,650 --> 00:05:19,720 Well, a lot of things with the use of macros. 80 00:05:20,110 --> 00:05:25,720 But basically, I didn't show you how you can change the name of your sheet directly by coding, basically 81 00:05:25,720 --> 00:05:28,870 by writing down a code that will allow you to do so. 82 00:05:29,740 --> 00:05:32,690 So basically, this is exactly what we are going to do today. 83 00:05:33,340 --> 00:05:35,370 So we are going to create a code. 84 00:05:35,380 --> 00:05:42,100 Well, very simple code that what it will do, it will change the name of this sheet and the name of 85 00:05:42,100 --> 00:05:44,560 this sheet as well at the same time. 86 00:05:44,950 --> 00:05:50,920 So you'll see it's pretty simple and it works pretty much the same way as when we're working with simple 87 00:05:50,920 --> 00:05:51,410 cells. 88 00:05:51,460 --> 00:05:55,300 So what I will do, I will simply delete everything that we have right there. 89 00:05:56,440 --> 00:06:00,100 So here in the last sheets, I have all the other projects that we have. 90 00:06:00,100 --> 00:06:02,000 So basically I have those projects right there. 91 00:06:03,010 --> 00:06:03,890 So pretty simple. 92 00:06:04,450 --> 00:06:04,780 All right. 93 00:06:05,200 --> 00:06:07,550 So let's try and do it. 94 00:06:08,170 --> 00:06:12,310 So first thing that I will do, I will just create myself another module. 95 00:06:12,340 --> 00:06:17,680 So once again, what you guys can do is simply click on insert and insert another module or simply delete 96 00:06:17,680 --> 00:06:20,170 the other macro that we have created. 97 00:06:20,800 --> 00:06:25,080 So when you have created another module, what we are going to start with is pretty simple. 98 00:06:25,990 --> 00:06:29,230 We're going to start out to start out with a procedure. 99 00:06:29,710 --> 00:06:31,330 So this is how we are starting now. 100 00:06:31,630 --> 00:06:34,780 So here we are going to call it ringin. 101 00:06:37,290 --> 00:06:37,770 Cheat's. 102 00:06:43,310 --> 00:06:43,650 Great. 103 00:06:44,030 --> 00:06:50,450 So we have our well, basically we have our document right here where our Michael and we are going to 104 00:06:50,450 --> 00:06:55,950 start the creation of our well, the name changing of our sheets. 105 00:06:56,510 --> 00:06:57,400 So pretty simple. 106 00:06:57,770 --> 00:07:01,840 The first thing that we want to change will be the active sheet in which we are right now. 107 00:07:01,850 --> 00:07:04,470 So let's say we are on our sheet number tonight. 108 00:07:05,540 --> 00:07:07,920 So we're going to start out with this sheet. 109 00:07:08,420 --> 00:07:09,260 So pretty simple. 110 00:07:09,260 --> 00:07:10,960 Do we want to change the name of the sheet? 111 00:07:11,150 --> 00:07:13,060 Let's say you want to change its name as well. 112 00:07:13,070 --> 00:07:17,510 So we want to change the name of the sheet are active sheet, the sheet seven and the sheet eight. 113 00:07:18,110 --> 00:07:19,500 So we're going to write it down. 114 00:07:19,520 --> 00:07:20,210 So pretty simple. 115 00:07:20,210 --> 00:07:21,550 How exactly do we change its name? 116 00:07:21,860 --> 00:07:27,410 So we're going to select the active sheet and what exactly we want to change. 117 00:07:27,890 --> 00:07:30,350 So we want to change the name. 118 00:07:31,040 --> 00:07:31,790 So here we go. 119 00:07:31,790 --> 00:07:39,200 Will write the name and there let's say we want to change our sheet number nine for project. 120 00:07:41,900 --> 00:07:42,380 No. 121 00:07:45,860 --> 00:07:50,360 Then so we change the name of our sheet, no, then then what will be the next step? 122 00:07:50,730 --> 00:07:56,070 Next step will be going to sheet number seven, jumping right there to be able to change its name. 123 00:07:56,450 --> 00:07:57,650 So this is exactly what we'll. 124 00:07:58,070 --> 00:07:59,550 So how exactly we can change? 125 00:07:59,570 --> 00:08:01,870 Well, how exactly we can do this jump. 126 00:08:02,210 --> 00:08:03,520 So it's pretty simple. 127 00:08:03,530 --> 00:08:06,430 We're going to use the select function in this case. 128 00:08:06,890 --> 00:08:13,910 So we're going to write down sheets because we want to work with our sheets and for our sheets where 129 00:08:13,910 --> 00:08:17,260 exactly what we want to go to sheet number seven. 130 00:08:17,270 --> 00:08:18,470 So we'll just write it down. 131 00:08:18,500 --> 00:08:23,450 So here will go to sheet number seven. 132 00:08:24,830 --> 00:08:25,300 Sragow. 133 00:08:31,020 --> 00:08:37,260 So when we are at that number seven, the next thing that we want to do is simply changing its name 134 00:08:37,260 --> 00:08:38,010 as well. 135 00:08:38,250 --> 00:08:41,820 And the how exactly do this I forget a little step right there. 136 00:08:42,420 --> 00:08:48,240 So we said that I forgot is the so basically I need to select my sheet number seven. 137 00:08:51,140 --> 00:08:52,010 Here we go. 138 00:08:57,730 --> 00:08:58,070 Great. 139 00:09:00,070 --> 00:09:04,840 So I have selected my sheep number seven now the next thing is pretty simple, we want to change its 140 00:09:04,840 --> 00:09:09,570 name, so we are going to use the same function as the function that we used a little bit earlier. 141 00:09:09,910 --> 00:09:11,740 So we're going to select the active sheep. 142 00:09:11,740 --> 00:09:16,900 So basically right now, since we're at sheep number seven, active sheep is sheep number seven, but 143 00:09:16,900 --> 00:09:21,580 instead we're going to change to change its name for Project seven. 144 00:09:24,720 --> 00:09:25,060 Great. 145 00:09:25,950 --> 00:09:31,740 So when you tell them what to do, we simply want to jump to seat number eight and do the exact same 146 00:09:31,740 --> 00:09:31,950 thing. 147 00:09:31,980 --> 00:09:37,290 So what we can do, instead of writing it down again, we can just copy everything and pass it right 148 00:09:37,290 --> 00:09:37,490 there. 149 00:09:37,800 --> 00:09:41,580 So instead of using sheet seven, we're going to use eight. 150 00:09:45,450 --> 00:09:49,210 And instead of having project seventh year, we're going to have Project eight. 151 00:09:50,310 --> 00:09:51,290 So pretty simple. 152 00:09:51,540 --> 00:09:55,800 So we'll do right now we are simply going to see if the code works. 153 00:09:55,810 --> 00:10:01,530 Well, we're going to look at the code instituted in mistake so I can see no mistakes for now. 154 00:10:01,740 --> 00:10:02,990 So we'll do right now is pretty simple. 155 00:10:03,000 --> 00:10:06,600 We're just going to try out our code to see if it works fine. 156 00:10:06,990 --> 00:10:07,650 So pretty simple. 157 00:10:07,650 --> 00:10:08,720 We can run it right away. 158 00:10:08,970 --> 00:10:14,580 And as you can see, we change the name of our sheet to basically sheet number seven became Project 159 00:10:14,580 --> 00:10:14,970 Seven. 160 00:10:15,300 --> 00:10:18,320 Our active sheet became sheet nine, Project nine. 161 00:10:18,330 --> 00:10:21,000 So here we have Project Seven, you have Project eight. 162 00:10:21,330 --> 00:10:26,290 And basically we changed the name of all our sheets simply with the use of only one macro. 163 00:10:26,430 --> 00:10:31,230 Of course, if you want to rerun it, it's not going to work because the sheet seven inch sheet eight 164 00:10:31,230 --> 00:10:32,290 doesn't exist anymore. 165 00:10:32,310 --> 00:10:37,270 So if we try it out, as you can see, it doesn't work, which makes a lot of sense. 166 00:10:37,800 --> 00:10:41,160 So as you can see, it's pretty simple to understand. 167 00:10:41,400 --> 00:10:48,330 So right now, if you let's say you guys receive a report or anything that you need to put in a certain 168 00:10:48,330 --> 00:10:53,310 format each and every day, you can feed yourself a macro to be able to do so. 169 00:10:53,310 --> 00:10:54,960 So you can see it's pretty simple to do. 170 00:10:55,560 --> 00:10:58,020 So to summarize what we saw in this class, pretty simple. 171 00:10:58,020 --> 00:11:07,500 We saw how you guys can clear some cells or a set of cells with the use of just one vehicle and how 172 00:11:07,500 --> 00:11:11,480 you can change the name of the sheets on which you guys are working. 173 00:11:12,180 --> 00:11:15,570 So that's at first class guys in our next class.