Excel VBA Basics #8 - Find the LAST ROW or COLUMN dynamically and clearing out your last report

[@JanBolhuis]

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!

[@aborucu]

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.

[@amrkay8907]

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

[@mahmoudfouad8524]

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

[@MutiBari]

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

[@ExcelVbaIsFun]

Glad it was helpful!

[@rosepink3613]

The best online VBA tutorial!!!

[@analbeadsinmybum]

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]

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

[@manonvandenoetelaar2761]

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

[@RamganeshR]

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

[@minyu5389]

Really good series of videos. Thank you!!

[@alicerunion]

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]

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]

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]

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]

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

[@alexball1995]

Everyone here was already using rows.count

[@aborucu]

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.

[@jakewilson5400]

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]

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

[@ExcelVbaIsFun]

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]

Amen

[@LordBransty]

Thank you! Great video!

[@jkarora4651]

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??

[@lisamendez1539]

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

[@hotsummerbeach]

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

[@ExcelVbaIsFun]

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

[@learn2shoot]

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]

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

[@Officeinstructor]

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

[@javedahmed8343]

Good one

[@Kaylinzhang]

thanks for introducing the shortcuts:)

[@ExcelVbaIsFun]

Very welcome!!

[@ExcelVbaIsFun]

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

[@Dr.Tree13]

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]

Good luck, MrGrape on your new project! Thanks Dan

[@Dr.Tree13]

ExcelVbaIsFun Thank you

[@LisaLeehopetrustdream]

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

[@amrkay8907]

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

[@excelisfun]

Thanks for the video!

[@BDQUERY350]

I appreciate so much your videos. parabens!!!

[@ExcelVbaIsFun]

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

[@rishikrsaw]

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

[@scottmarshburn5143]

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.

[@ExcelVbaIsFun]

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

[@5adamjb]

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?

[@rashmidhanda4370]

awesome and really helpful

[@ExcelVbaIsFun]

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

[@0429akming]

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]

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]

ExcelVbaIsFun thanks a lot!

[@ExcelVbaIsFun]

Very welcome!

[@ExcelVbaIsFun]

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

[@ExcelVbaIsFun]

Very welcome rialph!

[@5adamjb]

Really great stuff!

[@paoganum]

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]

can you show me? [e-mail] thanks!

[@abhinavkochgaway9343]

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!!!!

[@Kalithro]

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.

[@juanpasqualegonzalez]

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?

[@bredmond812]

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.

[@shlerTHEnumbas]

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

[@ExcelVbaIsFun]

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

[@ExcelVbaIsFun]

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

[@ExcelVbaIsFun]

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

[@jayjayf9699]

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

[@vegasvato55]

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???

[@cdeme123]

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]

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

[@roshnisconcepts2640]

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

[@ExcelVbaIsFun]

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

[@TheAsdftyui]

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]

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

[@geraldgiaimo5565]

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]

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]

Many Thanks! Excelent videos BTW

[@MohammadTaha]

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]

Thank you!

[@Myfrost1]

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?

[@veronicahuang8540]

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]

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

[@jamess.6703]

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

[@bencunningham4569]

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....

[@totleariss]

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

[@praveensingh1744]

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

[@ExcelVbaIsFun]

Thanks a lot tzbks1234! Will do. Dan

[@TheHollyville]

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

[@ExcelVbaIsFun]

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

[@optionsupdate]

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

[@ExcelVbaIsFun]

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

[@optionsupdate]

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]

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

[@optionsupdate]

ExcelVbaIsFun the file is uploaded, many thanks Sir

[@optionsupdate]

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

[@ExcelVbaIsFun]

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]

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

[@ExcelVbaIsFun]

Awesome buddy. Keep it up!

[@optionsupdate]

how do u get it to ignore formatting?

[@geonomix1]

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

[@rialphnocon299]

thank u sir =)

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

[@ExcelVbaIsFun]

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

[@ExcelVbaIsFun]

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

[@jayjayf9699]

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]

i have data in column A

[@jayjayf9699]

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

[@kimjin5815]

why the last column shows always Empty?

[@simonworkemail]

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

[@alexhwang3432]

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?

[@jayjayf9699]

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

[@jayjayf9699]

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

[@jayjayf9699]

Sorry I meant in* not interested in my first comment

[@ExcelVbaIsFun]

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]

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]

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?

[@age_of_reason]

Lickedy-split.