I have watched 8 videos now and still surprised by the clear way you explain VBA. The way others explain (by video or books) is often so much complicated after a few lessons, that it sometimes forces you to quit because there is no fun at that moment. But your videos are really fun. Hopefully it will all come together at the end of this serie. Thank you!

Thanks for the video!

Watching this in may-2017 i really appreciate what you did in this great course , i think this is the best tutorial for vb on youtube .. so thank you bro

Love this! Thankyou. You're making is so (!) clear.

Thanks for your amazing teaching technique, I'm new to VBA and your videos make any learner really comfortable.

Dear Dan, I tried to learn VBA code from books/some excel help sites some time ago even though I am quite profficient on formulas, left it undone.. I was waiting for you to take excel tutorials to the next level ! thank you and bless you as well.

Watching this in 2021 and getting value from it. Thank you !!

@ExcelVbaIsFun

2 жыл бұрын

Glad it was helpful!

Thank you for sharing wonderful videos. I have a question, if I would like to go to the next empty cell, what code I should enter??

That's great to hear Ram, glad that helps. Let me know if you have any questions about the content or projects you're working on. Dan

The best online VBA tutorial!!!

Thanks, Jan. It will come together, just experiment with the downloadable files. They're available in my dropbox video which has a link.

Thank you! Great video!

Really good series of videos. Thank you!!

Great to hear aborucu! Lemme know if there are any topics or projects you're working on that could use a hand. I'll do my best to help. Dan

Really great stuff!

many thanks for sharing these wonderful videos.... kindlly share these sheets also

Hi Daniel. I've been playing around with using this method in a macro that uses option explicit. In the case of this tutorial, if you were in option explicit what would you dim your variable Lastrow as?

awesome and really helpful

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

@babzo1

10 жыл бұрын

Amen

thanks for introducing the shortcuts:)

@ExcelVbaIsFun

7 жыл бұрын

Very welcome!!

Dear Dan, Thank you for your tutorial from which I learn a lot as a beginner in Excel VBA. I am stuck in a project where I generate reports for different weeks and each report is different is size. I am trying to generate a code that would copy whatever data I have starting always at B9 (same sheet, let's say sheet name is "Weekly Report") then copy and paste values with source formatting to another sheet ( named "General Report" ) at B9 also then clear the selected data in "weekly report" But when I generate another report for a different week in the Weekly report sheet and I run the code, the newly generated report will be copied this time to the general report sheet starting also in column B but ONE Row below the first one and so forth. All report are from column B to Column I but with variable number of rows. Please help me. Thanks Nabil Mourad

Very welcome rialph!

That's a good news, I'll watch your guitar lessons for sure :).

Really appreciate you taking the time to record and upload these. I've found the videos great to watch and very informative. If i was to suggest one area for improvement though, it would be a better name structure for your downloadable files. I couldn't find the one for this so i'm only watching and not doing. Thanks again and keep up the good work! :)

@ExcelVbaIsFun

10 жыл бұрын

Spencer, You're absolutely right. I'll have to take a weekend and get them organized. Thanks, DAn

editing video for you now. Will upload soon. Thanks! Dan

good morning, first want to express gratitude for the time and efforts of teaching people your skills. i was wondering if there is a way you can show how to delete lastrow and proceeding rows with a dynamic range. i have a report that has the same format but everyday the report changes with how many rows. thank you in advance. lisa mendez

I used the following formulas for the last row, last column and rows and columns after them. The rows or columns after them can be used in a Loop to recalculate. I do have a question about sorting data from least to greatest or A to Z as well as offset. I am not sure how to do these function. I appreciate the videos. I learned a lot from them. lastrow =cells(rows.count, 1).end (xlup).row lastcol = Cells(1, Columns.Count).End(xlToLeft).Column lastrow+1 lastcol+1

@xx-lb7ps

6 жыл бұрын

I don't know why when I used the codes from excelvbaisfun and other youtube channels it failed to work but your codes worked hahaha weird.....glad you commented though :D

Good Work Bro.. It is really helping me with Excel...

I keep having this error: Run-Time Error 9 : Subscript out of range, Could you please tell me how I can fix my code? Thanks so much

I appreciate so much your videos. parabens!!!

@ExcelVbaIsFun

7 жыл бұрын

Thank you! I appreciate your kind words!! -Dan

Sounds like fun. Keep watching and if you need help, send me a workbook with dummy data or real if you like, and maybe I can explain while making a video to help the vba community. Dan

at the moment I'm reviewing your tutorials to finish this : have a long list (i.e.25 clolumns long, 14.000 rows) of expenses with each personel's name in one column. I want to copy each personnels expense amount and name to different sheets and name the sheets with each personnel's name. in those sheets there are formulas which calculate a score for each personnel in a seperate table. last consolidate these scores for each personnel to a new sheet, (i.e. personnel name and score in two columns.

I think whole numbers like this should maybe be a double? or Long? maybe long is better. Again, I don't use option explicit. Thanks! Dan

Thank you for making such educational videos!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

@ExcelVbaIsFun

10 жыл бұрын

You are welcome! Thanks for enjoying them!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Good one

What is the advantage of ".count" and "End(xlUp)" over: x = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCell).Row ^ gives me the last row regardless of a column.

thank u sir =)

Can you just use eof property instead of this long statement?

Hey can you provide me with a snippet to fetch data from Unix box putty and place in the excel sheet directly using VBA...Plzz if could help me!!!!

What other code is in that macro? Or in that workbook? If you don't have room enough to paste it here, post this on our facebook page. You can also put screenshots and even upload the wb on there. Link to that is in the description. We'll help you through it! Dan

Thanks for these videos it is a great review for me. I do have a question - I entered the code in exactly as you have it here, but it fails on the "TheWorkbook." part of it. If I remove that it works, but I am wondering why it doesn't work.

@kristinechw1883

8 жыл бұрын

+William Clark I think it's supposed to be 'thisworkbook', not 'theworkbook'

Hi Dan, first Thanks for the videos, such a good help. I have a little trouble when it comes to writing the code ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row It seems its alright until the: ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1). But when i write End it shows ROW METHOD OF RANGE CLASS FAILED. (When I debug it shows the . between the .....Count, 1).(End(xl......... highlighted. Any ideas please?

Thank you Amr Kay, that's exactly what my goal is! More vids coming this weekend. Also thinking of doing guitar/piano/singing and music theory lessons on youtube. God bless! Dan

I have to watch this again 3 years later. gotta deal with a new excel project and obviously , python is not as easy as vba when handle cvs data.

@ExcelVbaIsFun

3 жыл бұрын

Good luck, MrGrape on your new project! Thanks Dan

@Dr.Tree13

3 жыл бұрын

ExcelVbaIsFun Thank you

Thanks a lot tzbks1234! Will do. Dan

is it one (number) or I (letter)? please check it out.

This is a little unrelated but how do you get the office assistant.. the guy standing at the back?

@ExcelVbaIsFun

9 жыл бұрын

Roshni Lobo Hi Roshni. I think the guy in the back was from www.incredimail.com - a free email service. But unfortunately he doesn't help with office. Yours, Dan

how do u get it to ignore formatting?

when I try to run InputDataVariable = txtInputText Range("B2").End(xlDown).Offset(1, 0).Select ActiveCell.Value = InputDataVariable I get this error "Run-time error '1004': Application-defined or Object-defined error" Now if I have cell "B2" has content in it, then the instruction seems to work just fine, but the instruction will not populate the first row... Any Suggestions of Comments???

Hi Dan, I don't know if you'll read this message since this video is kind of old, but I had a question: I copied in the exact same code as you to find the last row ( ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row ), and for some reason I keep getting an error message that says "Run-time error '438': Object doesn't support this property or method" Is this because I could be using a different version of Microsoft Excel, or is it because I've entered something wrong into the code? Thanks for all of your help, by the way! Your videos are wonderful and so easy to follow.

@ExcelVbaIsFun

9 жыл бұрын

Hi Alice, The information (what row is the last) has nowhere to go, so you must plop it somewhere, like a cell or a variable. Something has to receive the value. Copy this Alice last Row = thisworkbook.sheets ("myreport").cells (rows.count,1).row 'Make sure you have a sheet called myreport if you're telling excel that's where it's coming from as in above. Thanks Dan

@salyut

7 жыл бұрын

Hi, I used it like LastRow = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row but I get the exact same error message and I dont even know where to start debugging it. Thanks for all of your help!

@Seftehandle

6 жыл бұрын

Sub MacroRow() LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row End Sub -this worked for me I changed Row.Count to Rows.Count

@michael673

5 жыл бұрын

Still getting message as Alice is getting. I have all the statements you mentioned

@alexball1995

5 жыл бұрын

Everyone here was already using rows.count

How do other macros in the workbook affect creating new macros if they're not being used? I'm now worried about having to account for how many macros i'm making and what's in them o_o I also tried to copy the code presented and ended up with the 1004 error

This is a great video. by the way, do you know what the letters "xl" in xlup, xldown, (etc.) mean? I mean obviously they sound like "excel" but I see no reason the programmers of VBA would make that necessary.

Hey Dan, thank you for these videos. I have a question regarding finding the first column of a certain row. I thought of something like this: Firstcol = ThisWorkbook.Sheets("My report").Cells(13, Columns.Count).End(xlToRight).Column , the problem of this is that it gives me back Firstcol=9, when actually the first written column of row 13 is number 4. Could you please help me?

@ExcelVbaIsFun

10 жыл бұрын

can you show me? excelvbaisfun@gmail.com thanks!

Dan!! What if you wanted to have a way to sum at the bottom of a column. I have a report that changes length every month, and I want to be able to get the sum. I feel like I am so close. Here is my code. lastrow = ThisWorkbook.Sheets("TAG DYN").Cells(Rows.Count, 25).End(xlUp).Row ThisWorkbook.Sheets("TAG DYN").range ("Y2:Y"" & lastrow).sum The sum at the end is wrong, but is there a way I could get this to work? If you could just put the code that would sum your column B in this video

ThisWorkbook.Sheets("My Report").cells(rows.Count,1).end(xlup).row. Doesn't like the "row" at the end. I can do "select" and that works. Running Excel 2016.

@ExcelVbaIsFun

5 жыл бұрын

Chris Demeur Hi Chris, Please put this value into a variable, cell, messagebox etc. Ex: myVar = thisworkbook ..... row Thanks Dan

lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row this is what I got... and I keyed something in A1 to A10 just to test this... but lastrow kept = 1.... what did I do wrong?

Hi Dan. Thanks for the video! Would like to ask you a question a little unrelated. I want to create a code that deletes every page in my workbook except for one called "Paramaters" for example. How can i write this? Thanks

@ExcelVbaIsFun

7 жыл бұрын

Hi Gerald, Try something like this: dim ws as worksheet for each ws in thisworkbook.sheets if ws.name "Paramaters" then ws.delete end if next ws I would highly advise debugging this slowly the first time with F8 key to ensure things are spelled correctly etc. If that doesn't work, please reply or contact me on my contact form on excelvbaisfun.com Thanks Dan

@geraldgiaimo5565

7 жыл бұрын

Many Thanks! Excelent videos BTW

lastrow = ThisWorkbook.Sheets("myreport").Cells(Rows.Count, 1).End(xlUp).Row This does not move my selection box?

can somebody please help! I'm writing the code exactly how it is presented but when i'm writing LastRow = ThisWorkbook.Sheets("MyReport").Cells(Rows.Count, 1).End(xlUp).Row i keep getting the Run-time error '1004' Application defined or object defined error has anybody ran into the same problem?

lastrow = ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row When putting above code in I get subscript error out of range runtime error 9. Even though my sheet is named My Report....

Really appreciate your videos, it is a great tool for me, as a beginner, to learn how to use vba. However, i have some problems in 4:52, i could not type in the symbols you typed, is it the & ?

@ExcelVbaIsFun

10 жыл бұрын

Yes, Yau Shun Ming. Exactly. The Ampersand (&) symbol allows you to join 2 or more things together. Example: myWord = "Blow" & "fish" so myWord would take on the value of "Blowfish" OR Range("A1") = "The exact time is " & Time Which at this moment would pop the following into cell A1 "The exact time is 1:17 PM"

@0429akming

10 жыл бұрын

ExcelVbaIsFun thanks a lot!

@ExcelVbaIsFun

10 жыл бұрын

Very welcome!

xlup takes it up to the first cell with border formatted. but i want to keep borders

@ExcelVbaIsFun

9 жыл бұрын

optionsupdate What do you mean, optionsupdate? Can you show me your code or what is happening? Dan

@optionsupdate

9 жыл бұрын

ExcelVbaIsFun Sub drugsort1() ' ' drugsort1 Macro ' ' Range("O52:R71").Select Selection.Copy Sheets("Ennogen Goods in").Select Range("B2660").Select Range("b99999").End(xlUp).Select ActiveCell(2).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub this is the code, it takes a list from one sheet to another sheet, but I want info posted at first empty row, but xlup seems to make it stop in 5 -10 rows before empty cells, each time I use this the cell gets pasted lower down leaving bigger empty gap. Yours gratefully.

@ExcelVbaIsFun

9 жыл бұрын

optionsupdate Would you be willing to show me a sample workbook? You can upload a file here. www.excelvbaisfun.com/contact.html Thanks

@optionsupdate

9 жыл бұрын

ExcelVbaIsFun the file is uploaded, many thanks Sir

Why is it that I use this function and it doesn't go to the next available row??

I have put in the line like you show ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row but I'm getting an error that the "Object does not support this property or Method. Please help I'm a newbee and it is most likely me..Thanks

@ExcelVbaIsFun

10 жыл бұрын

Dear Charles, did you have this statement going into a variable or something. If you put ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row on a line of code, it doesn't know where to go, so you have to trap the number into a variable. So try this: charlesNum = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row This way the number will be trapped in the variable charlesNum, and when debugging, you can see that charlesNum has a number within it. Best, Dan

why the last column shows always Empty?

My first line is lastrow=Book1.sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row I kept getting the Run-time error '424'. What went wrong with my code?

@ExcelVbaIsFun

9 жыл бұрын

Veronica Huang probably get rid of Book1 and replace it with ThisWorkbook So either lastrow=ThisWorkbook.sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row OR lastrow=Workbooks("Book1").sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row Thanks! Dan

Your tutorials are awesome and quite intuitive but there are lots of add popping in between for subscription that's very annoying.

how can u get the visualbasic window to appear in bottom half of worksheet?

@ExcelVbaIsFun

9 жыл бұрын

optionsupdate Just collapse it and drag and drop the boundaries. There's 3 buttons in windows applications. leftmost is minimize, middle is expand/collapse, right is red X to close application. Use middle one to collapse and then resize as needed.

@optionsupdate

9 жыл бұрын

thanks very much, I have learnt so much after watching only 3 videos

@ExcelVbaIsFun

9 жыл бұрын

Awesome buddy. Keep it up!

what about this structure: lastcol = ThisWorkbook.Sheets("My Report").Cells(1, Columns.Count).End(xlToLeft).Column it works on the same principle; to find the number of the last column without referring to selection Good luck :)

@hageadan

10 жыл бұрын

Thank you!

Make sure End(xlUp) uses only letters: "l" is a letter; NOT number 1

Starting a new channel for Piano Lessons, learn by Chord and play by ear! Check it out here: How to Play Piano by Chord - Beginning Chords - C

What's wrong with NumberRows = Application.CountA(WorkSht.Range("A:A"))

@ExcelVbaIsFun

9 жыл бұрын

Seán O'Nilbud Try application.worksheetfunction.counta rather than application.counta Dan

Just incase anyone is interested. I reasently came across a situation where I needed to find the last row in a worksheet that had formulas Like=IF(B2="","",B2+C2) this was copyed down to row 500. The method shown here resulted in the last row as row 500 So I did some reasurch for a alterntive method and found this code.. With myWs Set FOUNDRANGE = .Columns("A:A").Find("*", After:=.Range("A1"), searchdirection:=xlPrevious, LookIn:=xlValues) If Not FOUNDRANGE Is Nothing Then MyWsLr = FOUNDRANGE.Row End With If MyWsLr = 1 Then MyWsLr = 2 End If (myWs is the variable set for the worksheet) (FOUNDRANGE is also a variable) (MyWsLr is the variable set for the last row on this worksheet) This should find the last row in column A in the worksheet excluding any formulas.

Why don't you put last row interested quotation marks; range("a2:c & lastrow")

@jayjayf9699

6 жыл бұрын

Because it apart of that cell range. Also when using variable why is it that they do " & lastrow &"

@jayjayf9699

6 жыл бұрын

Sorry I meant in* not interested in my first comment

@ExcelVbaIsFun

6 жыл бұрын

Hi Jayjay F, lastrow is a variable and thus it changes values. We don't want the word lastrow as part of the range, it might look like this to Excel once it's done calculating: range(a2:clastrow), instead of a2:c20 or whatever value is currently found in the variable lastrow. Thanks Dan

@jayjayf9699

6 жыл бұрын

ExcelVbaIsFun is it because one is a string and the other is a variable, so range("a1:c20") inside brackets is a string so variable has to be out side quotation marks?

@jayjayf9699

6 жыл бұрын

ExcelVbaIsFun is it because one is a string and the other is a variable, so range("a1:c20") inside brackets is a string so variable has to be out side quotation marks?

lastrow = thisworkbook.Sheets("My Report").Cell(row.Count, 1).End(xlUp).row im getting a object required error 424, how do i fix this, the full code is : Sub hjj() lastrow = thisworkbook.Sheets("My Report").Cell(row.Count, 1).End(xlUp).row End Sub

@jayjayf9699

6 жыл бұрын

i have data in column A

@jayjayf9699

6 жыл бұрын

never mind i have figured it out, cell should be cells (plural) and row should be rows (plural)

Lickedy-split.