Excel VBA - Drag Down Formula (Autofill) to Last Row of Data - Part 6
HTML-код
- Опубликовано: 18 дек 2019
- Learn how to add formulas in spreadsheets using Excel VBA code. We'll add formulas and then drag them down to last row in our data.
This tutorial is Part 6 from Excel VBA Programming - Beyond Recording Macros series.
• Excel VBA Programming ...
#excel #vba #tutorial
A million thanks! I lost track of how many tutorials I watched before I found this one. Parts three and six of this series provided me with the pertinent tools I needed to modify and customize my own macros. I love this entire series.
You did a great job! Thank you so much!
Thank you for the clear steps. I am able to automate my work and saving minmum 1 hour every week with this. :)
This did exactly what I needed! thank you so much for this video!
Glad it was helpful!
thank you so much hommie, I struggled with this for half an hour:)
Thank you so much. Very well explained.
Thank you so much bro ! This is exactly what I needed.
Thanks man!! You are the savior..
Hola amigo, muchas gracias por tu video es lo que estaba buscando hace mucho tiempo, aunque no entiendo perfectamente el ingles pude arreglar mi macro gracias a tu video, muchos saludos desde Colombia, Hello friend, thank you very much for your video, it is what I was looking for a long time ago, although I do not understand English perfectly, I was able to fix my macro thanks to your video, many greetings from Colombia
Thank you so much... Very informative 🙏🙏🙏
Wow, I struck gold here. So much useful information in 15 minutes. Thank you
Great to hear!
Just joined......thanks so much for that "lr" line which saved me today!
Thanks a lot for sharing this video. Very nice and helpful, sir.
Thank god you exist. You just saved me a bucket of frustration :)
Thank you very much !!
Amazing work on your video. Appreciate the detail and clarity on explaining each step
👍
Super helpful!
thank you very helpful
Very helpful! The extra rows on auto fill were making me crazy!!!
👍
Very useful. Thanks.
Exellent.....from india greate job
Many Thanks for this information...I tried and got success.
I just came across your lessons. Although my English is not good, I can easily understand your lessons. (Even better than the teachers who teach in my own language) Please continue with VBA lessons. Additionally, I have large table with a lot of data, and when I apply the formula like here, the macro takes a long time to complete. Can you teach some code that we can use instead of "vlookup" for big table? (loop i think)
Thank you so much
Thanks, good video!
Glad you liked it!
Well Explained, I was looking for the exactly same. thank you so much and all the best 👍👍 Love from India
My pleasure 😊
Excellent Mate
Amazing video
thanks Mat , was badly struggling to for autofill
Ty man
How can you automate the "G2:G" or "H2:H"? is it possible to get this through the active cell? Thank you
thank you so muach
I was searching for this perfect
Awesome!
Good details sir... one doubt if i change the data to convert Table format how can roll out running, if i enter below row wise., the formula updated in VBA ... pls help us sir
thanks
Hi, Thank you for this amazing explanation, I just wanted to ask, is there a way where this works without copying the above formatting? How can I merge a paste special (formulae only) code
.AutoFill Destination:=yourrange Type:=4
@@ExcelGoogleSheets : wow ! You responded so quick ! Thanks so much for your help :)
Although I found this out 😊 but now I knw whom to approach for my excel issues ! Gladly subscribing !!
Auto fill Range did not work for me because i notice i have some blank cell if there way i can fix that so the auto fill work till the end ?
Nice ji
How can I do this but instead of finding the last row on the sheet, find the last row of just a single column, or range of columns?
Can you help me with a VBA code, I want data to auto drag down. For example - if have values in column B , comes in every 5 min gap automatically with help of Macro like B2, then comes in B3 and so on. I have to check the difference of values in column C so for that I have to subtract B3-B2 to get current value as values are coming in every 5 min gap on B column so I have to drag down C column Manually in every 5 min gap for having current value. If you can help me with a vba code which can perform this automatically . It should a formula that if their is no value in B column then it should not come.
Hello and many thanks for this helpful video.
It has helped me a lot with my excel table. I have one question about this: If the number of the rows from my table is not the same always (for example: now i can have 2, next week 10, then 8 etc.) how i can change the code to match the number of the rows i have ? For now if i have 10 rows and run the code it will drag the formulas until the last row, but if i remove some rows from my table and run the code again, it will leave the same 10 created rows (with error for the rows which have been removed - because there is no data) and not update to the new number of the rows from the table. Sory if i have made some mistakes in my english.
The answer is in the video. Please watch the whole tutorial.
@@ExcelGoogleSheets I have watched the tutorial and made all the steps but what i wanted to say is that i needed to make the code to delete automatically the formulas in the right of the the removed lines from the table, then recreate them only for the available rows, without manually deleting the formulas and then to run again the code to autofill. In my column A i have a pivot table which is updating automatically with some calendar dates as I insert or remove rows from another table. From column B to F I am generating data with the VBA with a command button (some are workday formulas based on the pivot table and some are just text values). I have succeeded to make the code to delete rows based on blank cells in column A. I had only to write under the "Sub addFormulas()" the code "On error resume next_ Columns("A") .SpecialCells (xlCellTypeBlanks).EntireRow.delete" and then the code from you. Now it works the way I wanted.
I am a beginner at VBA so my brain generated a lot of smoke thinking why it is not working and how should i make it work :)).
I am really thankful for your work because i would not even made it this far!
I see, that wasn't clear from your comment. I'm glad you were able to make it work.
@@ExcelGoogleSheets I tried and couldn't get the lr to equal the row - kept saying empty
@@hollysymosky2580 There is no universal way to get the last row, it depends on your data. Please watch this to better understand where and how to use each method ruclips.net/video/NrYDAEsYcbU/видео.html
hi, i made a dynamic range based on adjacent column, but could not correct result. may i know how to get it for the following code.
Sub addformulas()
Dim my_sheet As Worksheet
Dim last_row As Long
Set my_sheet = ThisWorkbook.Worksheets("STR")
last_row = ThisWorkbook.Worksheets("STR").Cells(Rows.Count, 28).End(xlUp).Row
Range("AC5").Formula = "=ln(AB4/AB5)*100"
Range("AC5").AutoFill Range("AC5:AC" & last_row)
End Sub
I've been searching all around: Do you have a video where you show how you could do a function with this situation: if say SUM(B4:B36) is greater than SUM (D4:D46) Then display message "Unshipped"? it would come in handy for the sheet I use for my business for when we collected more revenue than we shipped out, and also for displaying a message "SOLD OUT" when the sum of a column is too large. Thanks
Just use a formula =IF(SUM(B4:B36)>SUM(D4:D46),"Unshipped","")
@@ExcelGoogleSheets Thank you so much! One more question: How would I do something where IF and only if a certain formula SUM(B4:B5)*35 happens to be negative, THEN display the answer to said formula from before (SUM(B4:B5)*35) , but divided by 2? So unless the original formula is negative, it does nothing, but if it is negative it divides the original equation by 2
ruclips.net/video/hG5vKMb0Lpo/видео.html
@@ExcelGoogleSheets Thanks man! Earned my sub
Hello! How can I sum cells from another sheet in the same workbook? I am using your lr formula but I’m stuck on using SUM in a dynamic way. I need to sum from another sheet “H7: H lr”
Probably something like this assuming you did set the "lr" correctly.
“=SUM(H7: H" & lr & ”)"
I'm afraid that the line for getting the last row is not working lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row . I get a "Run-time error '91': Object variable or With block variable not set. Any ideas as to what I'm doing wrong?
There is no universal way to get the last row, it depends on your data. Please watch this to better understand where and how to use each method ruclips.net/video/NrYDAEsYcbU/видео.html
but what if the data range change in the same sheet instead of multiple?
Can we use .autoFill with specialcells? I want to apply Autofill to only filtered rows which are visible.
I don't see why not.
Is there a way I can do this for 50 columns? each with slightly unique formulas?
Sure. Just repeat it for each column.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("A1") "" Then
Range("A1").Select
Selection.AutoFill _
Destination:=Range("A1:A5"), Type:=xlFillDefault
'.Range("K5").Formula = "=IF(AND(F5="",G5="",H5=""),"",(I4+F5-G5-H5))"
End If
End Su sthere a way I can do this for 50 columns? and its not appear can pls correct for me
How to do this if wanted to automate it that every data entered on A to F it would auto populate on G and H even without the sum? And without assigning a button for it? Thank you.
Have you tried using a table? Insert->Table
@@ExcelGoogleSheets I haven't tried that. However, would that help? haha. What I need is every time I enter data on the left ( a to f) the right( g to h) would show the results of the specific formula without clicking a button or dragging the autofill. I as able to do it on Vlookup but not this one. :) thanks.
@@raymonddose12 I think it should solve your problem.
@@ExcelGoogleSheets i will try this... the file is the "raw" data sheet and the answers are being converted to 1 if No and 0 if Yes via formula on the left columns so that the pivots on the next sheet would read it.
I have a question on Excel VBA but somewhat unrelated to this video. When typing VBA code intelligence always appears within brackets but certain commands are required to write without brackets. e.g. AutoFilter where Field and Criteria parameters are written without brackets. What is the indication to decide within brackets or without brackets? Appreciate your explanation.
I assume you mean parentheses () ?
You need to have parentheses if the function has a return value. In other words if you say
z = doSomething("parameter") it returns a value which is then stored in z variable.
On the other hand
doSomething "parameter"
is a subroutine with no return value and nothing to store in a variable
I hope that makes sense.
@@ExcelGoogleSheets Thank you for the prompt response. I think I did not explain my issue well.
I wasn't referring to Functions but to VBA code inside sub routines. Some examples (selected lines only) that I could think of:
1. Set SelectedWb = Workbooks.Open(FileToOpen(FileCnt))
2. DataRange.AdvancedFilter xlFilterInPlace, CRange
3. .AutoFilter Field:=9, Criteria1:=myRegion
when typing the code inside VBA Editor, automatic prompts after keywords always come inside parenthesis. However when typing the code, only certain words are typed within parenthesis and others are not.
In line 1 after Workbooks.Open there is a parenthesis. In lines 2 and 3 after words AdvancedFilter and AutoFilter there is parenthesis.
My question is when typing the code what's the clue that the next word should be typed inside parenthesis or not.
@@veebee3969 I knew what you were asking.
Workbooks.Open(FileToOpen(FileCnt)) is a function and it returns a value, therefore you save it in a variable SelectedWb. So the line is
Set SelectedWb = Workbooks.Open(FileToOpen(FileCnt))
DataRange.AdvancedFilter is a subroutine that has no return value, therefore we do
DataRange.AdvancedFilter xlFilterInPlace, CRange
in case it did have a return value, you would need to save it in a variable & the line would look like this
results = DataRange.AdvancedFilter(xlFilterInPlace,CRange)
@@ExcelGoogleSheets Thank you for time taken to clarify. It's a great explanation. Being a beginner this variation between function and subroutine is very subtle to me but I'll try to be more conscious. Thank you once again.
It's not just you. In any normal language you would always use () at all times, but in VBA everything is just a little bit extra weird.
Range("C9").FormulaR1C1 this statement is getting highlighted with an error Invalid use of property. what should i do??????????????????????????????????????????
I must have done something wrong. Right from the start, when I press F8, I get variable not assigned error and it highlights the lr =.
Check if you have "Option Explicit" on the very top of your script. If so remove it.
@@ExcelGoogleSheets I’ll check it out. Thank you.
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
efficient method
Thank you
I added this, but if there is data only in the second row and nothing else, it returns an error, can you please check. Its probably due to auto fill, great video though.
If autofill is the reason then you should be able to just simply add an if statement to only do autofill when there are more than 2 rows.
if lr > 2 then
'autofill lines go here
end if
How to use VBA When your range is dynamic by row and columns and also How to auto fill formula preset in the last column till the last row
Instead of Range("A1:D5") you can use Range(cells(1,1),cells(5,4))
5 is the last row number, 4 is the last column number.
watch these videos to see how to find last row and column ruclips.net/video/NrYDAEsYcbU/видео.html ruclips.net/video/OZLDN4DHn3U/видео.html
@@ExcelGoogleSheets so will it be Range(cells(1,1),cells(lr&, & lc))?
Showw!!
Hi CT, i'm your great fans concerning your videos of google spreadsheet Javascript i give you one more time a big thank about that. Concerning this serie of VBA, i don't agree with your method to teach VBA. This is not VBA, this is not the way to write VBA code. My opinion, nothing personal. Thank anyway for your time.
@@mark99k Actually you are right (It's definitely VBA). This code written and explained by CT, respect the title of this video, "Drags Formulas Autofill". As you know there are many ways to get a result writing a code. If would get the same result, i'd use loops, and if statements, but i repeat, "this is just my opinion" and sometime is better don't manifest it.
Eiger67 thanks for your input. First, I take no offense from your comments.
A couple of notes.
1. Although, there are better ways to handle this once you have more VBA skills, you should not be using loops for this. Using loops to apply a formula in a spreadsheet is very slow. You could read the data into array and then loop though the array, get your results and put the resulting array in the spreadsheet, assuming you don't need the formulas. But imagine how complicated that would be for someone new to VBA arrays.
2. I create my content based on my real life, in classroom teaching experience and seeing what results students get based on their current skillset. What I've learned in teaching is that most people give up learning when it takes too long to get some real results. This is intended for people new to VBA and programming. I have made my JavaScript Apps Script basic series with the same in mind and it seems like it worked for you. I never write my code the same way I teach in my Apps Script series BTW.
@@ExcelGoogleSheets Thank you, for your reply,, i'm sure there are many people learning from your videos besides me keep doing .
But how do you select active cell as the start of range to autofill?