Sure, Harish. Can you copy/paste the code you used? Also, sometimes you have to be careful which column you're using. If you have that directed to something other than col 1 and then you're meaning for it to check col 1, it sometimes will give you trouble. Here's the basic format: lrow=sheets("Sheet1").cells(rows.count,1).end(xlup).row Talk atcha soon, Dan
@ExcelVbaIsFun
10 жыл бұрын
Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
@ComedyChoice
6 жыл бұрын
Thank you bro, Pls My Channel,Like Share & SUBSCRIBE
@danstrong5354
11 жыл бұрын
That's a great idea! Use a custom function to get the last row on the sheet that you use often!! Brilliant! Dan
@Nino9782
11 жыл бұрын
Great work with the videos! Unless I'm doing something wrong I noticed this doesn't work if your data is structured as a table.
@ExcelVbaIsFun
11 жыл бұрын
Hmmm. What do you mean Doug, about refreshing? They typically recalculate just as often as built in ones. What problems are you having? PS I had a wonderful professor at Rhema Bible Training Center in Tulsa OK named Doug Jones, great name!
@excelsocallmaster
11 жыл бұрын
Hi dan, excellent video just one point that I'm sure you know anyway use Application.Volatile at the first of your UDF will update when workbook is recalculated
@couponcharlie
11 жыл бұрын
I want more!! These were great. Thanks for dedicating your time!
@ExcelVbaIsFun
10 жыл бұрын
Carlos, after much avail I figured it out. Remove your function from the Sheet and into a MODULE. Apparently excel doesn't like Functions in a sheet or ThisWorkbook object, but rather in a Module. It's kinda picky. Thanks!
@ExcelVbaIsFun
11 жыл бұрын
I'm not familiar with that approach, do you have a sample of the code or a sample wb I could look at? Thanks! Daniel
@scotolivera8207
4 жыл бұрын
Thanks for the video . But about the tool tip any ideas ?
@anveshkoripalli
4 жыл бұрын
Thank you so much for your videos , it is very easy to learn, I have one issue : I have a data where i need to use that data to submit a ticket,. I managed everything , Open the explorer put the required data over there and also submit , but before submitting i need to attach a file code is able to copy the file name from excel , click the browse button on explorer , but it is unable to paste and click open on the dailouge box , i am doing it manually. can u help fix that ? (Im using IE automation as i do not html to do it on firefox)
@mmatss
6 жыл бұрын
Thanks for another great video! Two questions: how is the syntax for finding LastrowC here .End(xlUp).Row instead of .End(xlDown).Row? Also, in the end of the video you added more dates to the third column and the cell with the custom function did not update until you ran the custom function again manually. Did you have the automatic calculation disabled from application options, or why did it not update the cell value automatically?
@ExcelVbaIsFun
6 жыл бұрын
Hi mmates, the syntax here is a little better for finding last row, because when using xlDown, if there are any blank cells, it will stop at the cell above the blank, if you start from the absolute bottom cell in the whole workbook and go up to the nearest non-contiguous cell then it will find the last row accurately. The other question is wonderful also! I think it wants an actual cell change event in order to recalculate, not just clicking around or exiting a cell that hasn't changed. I had calculation to automatic. Thanks Dan
@ExcelVbaIsFun
11 жыл бұрын
More are on the way, sir! Thanks for your time in watching and thanks for taking the time to make a comment. Please "Like" any videos you think are cool! Lemme know if you have any questions, sir.
@Odysseus314
7 жыл бұрын
Nice example and well presented :) I have just one question: Why isn't the lastrowC function autoupdated after you change the lengt of column C - and is there any way to make a function that actually updates automatically when changing your sheet data? Kind regards!
@ANgocBD
6 жыл бұрын
I think you try ckecking on Excel Options\formulas and calculation option, you select Automatic
@TheScottek9
11 жыл бұрын
I used this code- Function Lrow() Lrow=Sheets("Sheet2").cells(Rows.count,1).end(xlup).row End function I have my data in Sheet2..
@JanBolhuis
10 жыл бұрын
Hello Dan, Thanks for sharing your knowledge. Is it possible that is will also work without define selectedcells as a range? Here is how it works with me. Or are there any disadvantages if you don't use is as a range? Function LastRowC(selectedcell) sc = selectedcell.Column LastRowC = ThisWorkbook.Sheets("Functie").Cells(Rows.Count, sc).End(xlUp).Row End Function
@ExcelVbaIsFun
10 жыл бұрын
Jan, anytime a variable isn't defined, excel does it's best to figure out what you want it to be based on context. It's automatically a 'variant' type which is a generic variable that can take on several forms. If it works, more power to ya, if in doubt, experiment with selectedcell as variant if you have to define it.
@MohammedKalache
9 жыл бұрын
hey there, thanks for the vid, i have one question , can we have a help bar when we are using the formula, like the one in Sum(Range 1,Range 2 , ... ) ?
@mr.kamalsah4197
6 жыл бұрын
Hello Sir, I have one queries of, I have created some of code for my own function but, it doesn't work if I one new file... Is it like that of, new created function will only work in file you saved it...???? Waiting for your response sir...
@TheScottek9
11 жыл бұрын
Hey Dan... when I use the Last row function in Excel i get zero instead of the last row number....!!!! Can you help ?
@vibhavarivyas5855
7 жыл бұрын
Hi. I am trying to create a same vba function and it is not working When I go to execute the function in my spreadsheet it is recognized, but It returns "#NAME?" When I hover my mouse over the text I get "This formula contains unrecognized text" When I hover my mouse over the stop sign looking button thing it says "Invalid Name Error" When I try same function in another sheet it works perfectly fine. Even it works in the another cell in the same worksheet. I also tried clear formats for the cells in which this gives above error. Can you pls shed some light on this?
@excelisfun
11 жыл бұрын
Thanks for the video!
@uncommonsense6022
5 жыл бұрын
I am wondering if there is a way to get around having to double click on the cell for the value to change when its inputs have changed? I ask because I am making a function that I would like to apply solver to, but it wont work if the cell value does not update with each iteration. Any suggestions?
@needthistool
3 жыл бұрын
Put "Application.Volatile" or "Application.Volatile True" at the top of your function.
@AF89genesis
7 жыл бұрын
how do you add yellow circle around your cursor in Excel????
@faustprivate
6 жыл бұрын
You can't. It's the program he uses to capture the screen video.
@MohammadTaha
10 жыл бұрын
why does not it go into break mode when I press F8 inside a function code?
@LeePartington51
10 жыл бұрын
Put your stop on the code line as indicated in video then run function from spreadsheet. Excel will return you to your code window which you can then debug as normal
@jayjayf9699
7 жыл бұрын
Why have you bot declared sc as a variable (Dim sc as integer) when do you have to declare variables?
@jayjayf9699
7 жыл бұрын
Not*
@tajedeenown2867
10 жыл бұрын
Very useful thank you
@ahmedzakikhan7639
10 жыл бұрын
The Lastrow function is giving the last row number . How can I find the actual value of the last row?
@ExcelVbaIsFun
10 жыл бұрын
Ahmed, it depends which column you want the value for. Let's say you wanted column one, for example. myVar = thisworkbook.sheets("Sheet1").cells(lRow,1) if you wanted column 16 of the last row, you'd use myVar = thisworkbook.sheets("Sheet1").cells(lRow,16) Yours, Dan
@ahmedzakikhan7639
10 жыл бұрын
ExcelVbaIsFun Oh, cool. Thanks.
@ExcelVbaIsFun
10 жыл бұрын
You're very welcome.
@ExcelVbaIsFun
10 жыл бұрын
Very useful tip, thanks!!!
@JanBolhuis
11 жыл бұрын
Thank you so much
@FranciscoValenzuela92
9 жыл бұрын
Where do we get the same sample workbooks to follow along with ease?
@ExcelVbaIsFun
9 жыл бұрын
Francisco Valenzuela The wbs are available in the dropbox link in video descriptions.
@FranciscoValenzuela92
9 жыл бұрын
Got it :)
@dallapry
8 жыл бұрын
+ExcelVbaIsFun bdmgkg
@dallapry
8 жыл бұрын
qahn
@Sunriseto
8 жыл бұрын
+ExcelVbaIsFun Where is the dropbox link in video descriptions? I seem have trouble to find it. Thanks
@asirivishwajith7762
6 жыл бұрын
Thank you!!!!!!
@age_of_reason
5 жыл бұрын
I appreciate all your time however this video you are assuming the learner knows things. Much of your instructions leave out details. You imply too much here. Aside from this criticism I really appreciate your efforts and thanks a lot:)
Пікірлер: 48