hi this is Victor from Launch Excel. In today's episode we're going to have a
look at four ways to use VBA to find the last cell, row or column in your Excel
worksheet. So the first way is Range.SpecialCells, the second way is Range.End
the third way is Range.Find and the fourth way is to create a
user-defined function in VBA. So stick with me and today we'll learn these four
different ways... right before we get started with the actual video tutorial I
got a quick announcement to make. when you finish watching this video you want to
read more detailed instructions to go with each of these different methods
then go to my blog post because it's got the full VBA code there that you can use
in your own macros and the link to my blog post is in the description below the
video - so check that out after you finish watching this video. All right let's
check out special cells. So here I've got a sheet with a few rows of data in it
and I'm going to show you what this range dot special cells does and it does
exactly the same as pressing ctrl + end on your keyboard so I'm gonna hit ctrl + end
now and it takes me to cell d6 and if I hit ctrl + home it goes back to a1 so
range of special cells is exactly the same as hitting ctrl + n and I'll show
you how to use it it's pretty simple I've just gone into the VBA editor with
alt + f11 that's a keyboard shortcut what I'll do is I'll specify range and I'll
start with a1 the top left cell i'll type in dot special cells
press tab here and excel cell type or select all the way down to last cell
press tab close the bracket and i'll just select it so i dot select and now
we'll go back into excel with alt + f11 and we'll run that macro or press alt + f8
and you'll see it's selected a underscore special cells
I'll hit run and it goes and selects cell d6 so that's just the same as using
ctrl home on the keyboard and as a quick note the last use cell is not the same
thing as the last cell with data in it so you see this d6 that has data in it but
I could change say cell f8 to the last used cell fighters
right-click in it and I select increase the font size by one that doesn't look
like there's anything in it and the only thing that's changed is a bit of
formatting but when I go back to a one and I run the macro again or f8 and run
you see you didn't select that last cell with data in it it's selected f8 and it
doesn't have any data in it just had the formatting change so using this special
cells if I go back to the VBA editor alt + f11 if you use this dot special cells it
will select the last used cell which exhale defines anything with a change
made to it and it can be useful if you go back in here if I want to delete I'd
say that cell - select E and F and hit ctrl - to delete that means it should
reset the last used cell instead of f8 it should be now d6 but it only refreshes the
last used cell range if I save the file so I hit control + s now to save it and
I'll go back up to a1 hit Alt + F8 to run that macro now it goes back and it selects d6
so when you press save or you actually save in VBA what Excel will do is reset
the used cell range but there's another way to do that so if I go back into the
VBA editor if I say select this I'll insert one line on this write active
sheet dot used range and that line here let's comment this out that refreshes
the used range so you don't have to save it you can just use active sheet or
whatever sheet you're on thought used range and that will refresh the use
range so that was range of special cells use that to find the last used cell and
you can also use it to help remove unwanted unused cells so cells with no
data in them just change the formatting and is it actually the same as using
ctrl + end on your keyboard now range I'll find this searches single rows or
columns but the data set must have no blank cells and I'll see what this means
very soon so how does range lock end work let's have a look at this sheet
again and I'll explain using this crack so we assumed that this is the worksheet
we have let's say the last row in column a is 20 so what we do is this starting
here will go all the way up and we'll stop there where there's some data in it
and the same thing for Row 1 let's assume column L of the very last column
in this worksheet so we start there and then we work all the way back the left
here to d1 and so column D in this case would be the last column in row 1 that
has data in it so what we'll do is I'll show you an equivalent so that's how it
looks like when you draw it out but if I were to use keyboard shortcuts basically
go to the very last cell i pressing ctrl down all the way and then I press
control up arrow and it brings me to cell a6 so that'll be finding the last
cell in column a with data in it and the same over here if I press control right
arrow it will take me all the way to the right and what this range dot n does is
it emulates to control left arrow so if I do control left arrow you'll end me up
in the column D with end date and that's the same thing as what range of n does
so just remember it as the same as the keyboard shortcuts control up and
control down control right and control left let's have a look at the code now
so I've declared a couple of variables in this and the first thing we'll do is
we'll find last non blank cell in column a so we've got this variable last
underscore row for the last row and last underscore column the last column let's
go find the last row so last whoops let's delete that last underscore row
we'll set that equals 2 now cells I'm gonna specify the cell I want to have
the last cell a in column 1 which column name so rows dot count column 1 they'll
count the total number of rows on the active sheet code end and I'll choose
Excel up I could choose Excel up exhale down
same as pressing control up arrow control down arrow dot row enter and now
to find the last non blank cell and Row one will do the same thing but for
columns so last on the score column he called cells and I'll choose a cell all
the way to the right in Row 1 to Row 1 comma and columns columns dot count
close bracket dot and and here I'll choose Excel to left so that's the same
as control left arrow and Excel to write would be the same as control right arrow
and I'll specify the column number is what we want and then this message box
just outputs the last row with the last row number and the last column with the
last column number so we'll head back in to excel so we run with alt f8 and we'll
choose to run that first macro and what this is output is a dialog box shows
last row is 6 the last column is 4 so that's correct the last row is 6 yeah
and the last column is 4 earlier we did mention in this overview that this range
are ends such as single rows or columns and the data set must have no blank
cells so let's have a look at data set with blank cells and we're going to run
that same macro run now have a look at the output it says the last row is 5 and
the last column is 3 the last row is 5 here and the last column is 3 why it did
that was went all the way to the last cell here and it went up and it found
the last non blank cell is a 5 there an all the way to the right and left there
and they found the last non blank cell was c1 so in this case because there are
blanks in the data set he didn't find the actual last row and column that we'd
expect it to find so there's a bit of a warning and it would be ok if you had a
data set like this but do you have a data set with blanks in it be careful if
you use this method so the third option we have is to use range dot find and
that searches the entire range of sheet and it's ok with blank cells in a data
set so I said look how this works a little bit like using the Find and
Replace dialog box so here is the finder of this dialog box in Excel I just
pressed ctrl F to bring that up and basically what we're doing with finding
an asterisk and asterisk means any character in the cell
searching in the sheet by rows and formulas we don't need a match case we
don't need to match the entire Excel contents so I press find next we'll just
jump to the next cell that meets this criteria so we're doing something very
similar in VBA so here is a sub the purpose of this range find last row is
to find the last non blank row on the active sheet and there's a variable
declaration I'm gonna call at last underscore rows before and there's two
lines here one is a set the last row using cells dot find and the other one
is a message box output the last row and I just noticed a misspelling here so I'm
going to correct that last row all right so the cells not fine if I go inside the
brackets and I press ctrl I that'll bring up the information what we'll see
is these different parameters we're gonna use the first one the what second
one the after the third one to look in fourth one look at fifth one the search
order and the sixth one search direction that sounds quite a lot but it will be
useful to enter all of these in because every time you do a find it's gonna
default to whatever was your search for before if you don't specify each of
these parameters then it's gonna just use what was last used which may not be
what you want so it's important to specify each of these parameters when
you write this I'll start with the what so what we'll do is an asterisk
that's the wild-card character coma so I'm going to do an underscore and hit
the next next line so I'll do it after and I'll specify after range a one
because we want to start at the top left and then we'll go look in so what we do
for look in is we're gonna look into the Excel formulas Excel formulas
and look at we don't want to look at the whole cell we just look at the part of
the cell so anything that matches just even a part of the cell will be fine and
a search order search order we will specify as Excel by rows because we want
to search by rows and then search Direction search direction we want to
specify that to be Excel previous so instead of looking for the next one we
want to look for the previous one and at the end of that we'll type in a row and
there we have the VBA code that we can use so I'm going to press alt F 11 and
head back into Excel and I'll run that macro or f8 and run will see that it
outputs the last row is 6 so that's nice because it found the last row here I'm
gonna switch to the other spreadsheet now this or the other worksheet one with
missing data in and I run the macro again and you'll see this time it found
the last row is 6 as well and I'll show you briefly what it's doing if I just
draw this out it starts with cell a1 and the reason it finds this instead of
getting affected by the blanks is it starts in cell a1 it goes backwards all
the way back to the last cell and then it searches by row so it goes all the
way down this row can't find anything it goes down to the next row up and it
can't find anything and it keeps searching each row until it gets to a
row like this one here where it finds oh yes there's a cell with some data in it
and it returns that row number so in this case it's 6 that's why it's ok with
blanks in your data set so if we wanted to find the last non blank column on the
active sheet instead of the last non blank row we just switch out row for
column so here we got last underscore column we declare that as a variable and
we set last line of score column to be equal to cells not find same what same
after same look in same look at we just changed the search order instead of
searching excel by rows research excel by columns same search direction when I
go to excel previous so not the next occurrence with the previous
currents and we specified column and then we'll have a message box that says
the last column is then the last column number so that's how you do last non
blank columns so that was using brain shock fine to search the entire range of
sheep and it's okay with blank cells in the data set now quick intermission if
you liked the video so far please hit the like button below it means a lot to
me and all encouraged me to create more videos like this one so hit the like
button and you can also download the accompanying workbook from my blog post
this has got all the VBA code in it so you don't have to write out yourself you
can copy and paste into your own workbooks so make sure to check the link
to my blog post below it's in the video description right so the last thing
we'll have a look at today is user defined function so creating a
user-defined function means you can write anything you want to you could
create it how you want it to work in this case I've created one which can
specify the last cell or row or column depending on what parameter you put in
and you can also use this in a worksheet formula not just in VBA you can actually
use this on a worksheet and I'll show you both these things very soon so here
is a function of course that find last capital F capital L and there's three
parameters versus versus search type which is a string the second ones
optional so you don't have to specify this and this is STR for string
underscore worksheet so that allows you to specify the worksheet name and again
the third is optional this third parameter is STR underscore range again
a string that's to specify the range address the purpose of this fine last is
to find the last row column or cell in the specified worksheet in range
depending on what you specify the search type so parameters are row call and cell
and I'll just take you through how this works so the first thing you'll see is I
declare some variables the important part here this first parts the important
part I'm going to default the active sheet if the worksheets not specified
and I'm going to default to all cells if the range isn't specified which is why
these are these two parameters up here are optional so what happens is if the
string worksheet is not specified if it's just a blank then what we'll do is
we'll set this worksheet underscore search to be the active sheet otherwise
we'll set the worksheet underscore search to be work
Sheetz specified by the string worksheet that name that we put in its parameter
and then we end if that allows us to default to the active sheet if you don't
specify worksheet always set the worksheet to be the name that we put in
the parameter now we also want to default all cells if the range isn't
specified so instead of just searching an unknown will search for all cells if
so if if the parameter number three this STR is called range is blank what it
does is sets the RNG search it's a range search to be equal to the worksheet dot
search that's a bit of a tongue twister worksheet but search worksheet
underscore search even dot cells all the cells in the current worksheet search
otherwise we'll set the range underscore search to be equal to the range so their
worksheet underscore search range string underscore range and end if that does
the default into the active sheet this one does the defaulting to all cells
you'll notice as well briefly our dimensionless on error go to error
handler what happens is if you're trying to set the worksheet search or the range
search and there's an error you don't want the program to just throw a spanner
and say I don't know what to do this since that tells it to go to the error
handler and the error handler is down here the error handle just says message
box in this case very simple inés get output the message error setting the
worksheet will range and leave it up to the user to do something next right so
there is the first part let's have a look at the second part so this is the
interesting part what we'll do is we'll select case so depending on what the
user put in as the search type either the row or the column or the cell it
will jump to the case row or case column or case cell what I'm doing here is on
arrow resume next so that it just keeps going if there's an error all do is find
last so we've set the output of the function to be equal to RNG underscore
search and this is exactly the same as method three we had to look at before we
had the range dot find but doing exactly the same thing we're looking for an
asterisk which is the wild-card character to search for any character
looking after in this case we'll look after the first cell
and the range underscore search cells and we'll look in Excel formulas we'll
look at Excel part that Excel search order sorry is Excel by rows and the
search Direction is X all previous just like before I will find the row so what
that does is it returns the the last row with data in it and it outputs that in
the function I find last and then it leaves this and it ends the function
down here if I just scroll down a little bit so at that point it would go and
select and an exit function on the other hand if the user specified they wanted
to search for columns it would come to this point and the search height would
column we go down to case column and then it will find the last column so it
said the function output to be equal to range underscore search dot fine so just
the same as before we're doing range drop find will search for any character
who is this asterisk wildcard will search after range search dot cells one
to the first cell in our search range and then it will go look in the formulas
will look at Excel part cell not the whole cell and we'll do a search order
by columns and search Direction is previous so just like option three
before and we're going to do the same thing and return the column number so
then after it goes it finishes this it would exit the function now if the user
decided to specify the cell instead of the row or column the only difference
here is that I'm combining last row and last column so we first find the last
row and then we find the last column using the range dot find and afterwards
we combine that in the fine last so fine last is set to be worksheet underscore
search dot cells and we open bracket last row comma last column close bracket
address and that will return the address of the last row and last cell in that
search range I've put in false comma false here because I don't want to
return the absolute reference just a relative reference so the row be
relative and the column be relative what I mean by that is instead of
outputting say dollar a dollar ten which is the
absolute method we will just output a 10 and so there is the way we use this
combining the last row and the last column into the find last here it's the
fine last returns that address now a little bit of error handling here if the
error number is greater than zero that means there's an error what we'll do is
we'll set fine last to be equal to the range underscore search thought cells 1
dot address so we'll just return the first cell in the search range just in
case the whole sheet is blank in which case there's an error when you run this
it will not error out it would just return the address of that first cell
and then it clears the error right so that's how the VBA function looks like
let's have a look at it in action so quite example of using it in VBA I've
got this sub last underscore row underscore example and I'm just going to
declare one one variable last underscore row well find the last row and then
output it in the message box so the last row we'll set that equal to and here's a
function name find the last Open bracket I'm going to specify it in the inverted
commas row so that's the string that specifies I want the row not third
column or the cell comma so the second parameter I'm going to set as worksheet
table and third parameter I'll just leave this blank so remember we have
three parameters the first specifies what kind of search we're looking for
the second a worksheet and the third range and if any of these parameters are
blank either the worksheet or the range is going to go to the default values so
that's a simple example of how you use it for the row and let's go into excel
if I go into Excel or f11 I'm gonna choose this table worksheet the last
cells basic this one f-14 the last row B 14 I go run and with all f8 and I go
last row example run that it will output the last row as 14 so that's basically
how you use it in VBA and you can also use it on the worksheet so let's have a
look at this I've got this formula here actually says equals find the last which
is our custom function I've opened bracket C 3 comma C 4 comma C 5 and so
it's specified each of the parameters using values on this worksheet so C 3 C
4 C 5 what this does is it finds the last cell on this sheet table and in the
range A 1 to D 24 so a 1 to D 24 from here to here last cell with any data in
it it's actually cell D 14 and that's the result that's produced here so D 14
is that cell here that's the last cell in this range that has no that has data
in it so we can do the same thing for rows let's say we wanted to use find
last in this case D 3 is Rho D 4 is the same worksheet table D 5 is the range a
1 to D 24 all we could do columns by specifying Co L what we could do cell so
with the find the last function you can either use it in your VBA programs or
you can use on a worksheet which is pretty versatile and you can select row
or column or cell so a quick recap we looked at four different ways to use VBA
to find the last cell row or column including this very handy custom
user-defined function that you can even use in your worksheet formulas now let
me ask you a question since you're learning how to write macros in VBA have
you ever struggle to get a quick overview of key topics and Excel macros
in VBA well look no further because I've created these special one-page cheat
sheets that will help you when I was learning Excel VBA over 10 years ago I
wish I had these quick reference kiya so those little things I couldn't remember
so I created these special one-page cheat sheets and you can get your own
copies if you head down to the video description below you'll find a link to
these excel vba cheat sheets you can get them instantly so these are very handy
you can print them out and keeping your Peter they cover several key topics such
as General Excel VBA and Excel VBA shortcuts decisions and loops and more
to check that link in the description below plus if you haven't already
subscribed to my youtube channel make sure you hit the subscribe button and
click little Bell icon to be notified as soon as a new video comes up you can
also subscribe to my free Excel newsletter to get the latest updates on
my blog the link for that is in the video description as well and one last
thing if you want to read the detailed instructions that go with this video and
get a copy of the VBA code in the sample workbook that accompanies the video head
over to my blog post again the link is in the description below the video
thanks for watching launch excel and have a great day
For more infomation >> Building A Church Media Team From Scratch | Pro Church Daily Ep. #136 - Duration: 11:37. 
For more infomation >> Bên Trong Quả Bóng Thi Đấu FIFA WORLD CUP 2018 Có Gì??? Thử Bổ Ra Xem - Duration: 3:38. 
For more infomation >> (突發！)《PRODUCE 48》小櫻花排名急瀉，96名練習生「大換位」，最新排名公開 New Ranking Result Spoiler Eng Sub 180620 - Duration: 5:26. 
For more infomation >> Devil May Cry 5 - Bastidores com Hideaki Itsuno - LEGENDADO PT-BR - Duration: 2:32. 
For more infomation >> Tặng Quà Và Tâm Sự Với Fan - Sung Vlogs - Duration: 12:11. 
For more infomation >> КАК РОССИЯ ОБЫГРАЛА ЕГИПЕТ 3:1 И ВЫШЛА В ПЛЕЙ-ОФФ ЧМ 2018? [ЧТО ВЧЕРА БЫЛО?] ОБЗОР МАТЧА - Duration: 5:38. 
For more infomation >> Свежие анекдоты из Одессы! Анекдот про евреев и деньги! (20.06.2018) - Duration: 0:48. 
For more infomation >> 最新講座影片~2018 美國封閉式教學-調因(二) - Duration: 41:11. 
Không có nhận xét nào:
Đăng nhận xét