Next Empty Row Trick in Excel VBA & Macros

[@tuantrinhngoc3003]

Damnnnn. Your tutorial is so clean. It's like the perfect clear lake that i can see through all the way to the bottom

[@TeachExcel]

I'm glad you think so :)

[@rayvanderheiden3565]

Excellent tutorial! Good pace! Clear enunciation! Thank you for adding this to your list of tutorials. With limited experience in VBA programming, I have found there are many good tutorials on uTube for me to practice my skills. Looking forward to working through your other tutorials.

[@TeachExcel]

Thanks for your great comment Ray! I'm glad you liked it and I hope you find my other vba tutorials helpful :)

[@Red00022]

This is well well well made and explained. Little golden nugget right here.

[@camis1627]

This was the one that finally solved the problem I was having with updating the value of the first blank row without cutting off previous rows with no entry in the first column. Had to add an .end(xltoleft) so it wouldn't also update starting column 2, and then...eureka. Thank you!

[@KeyserTheRedBeard]

magnificent upload TeachExcel. I broke that thumbs up on your video. Always keep up the really good work.

[@TeachExcel]

haha thank you very much :))

[@KeyserTheRedBeard]

@@TeachExcel no problem at all. always happy to support my fellow content creators. i am looking forward to improving on my own current video format i have uploaded and, in the future, trying to create content as good as yours in the long term.

[@darrylmorgan]

Enjoyed This Cool VBA Tutorial..Thank You Sir :)

[@TeachExcel]

Thank you Darryl! I'm glad you liked it! :)

[@Barrys_Workshop]

Really helpful thank you

[@Kino9723]

Great video. very helpful. thank you

[@Innovlrnr]

Very smooth and clear explanation thanks for posting this video , please explain how to write code for deleting rows from next empty cell to last row of sheet

[@abbywilkins3966]

Love this video, so clear, how best would I use this to pull through the next unused order number and input this into the order sheet template when being created?

[@triesjeflagg5709]

Hello. Do you have the interactive online class to teach macro and vba? Thank you so much for sharing the tutorial.

[@BassamThebian]

Hi, Thank you very much indeed, you were very clear in that illustration. I have a problem though, I'm using "for" function in my vba it worked perfect in one data sheet but in the second it's not stopping on empty lines I set the for function for 6 lines and I need it to take the non empty cells in those 6 rows but it's not its always taking the whole 6 rows even when they are empty. Any suggestions?? thank you in advance.

[@kevincoleman7164]

Can you a bolder font in when your in the VBE for people who's vision is not that sharp like me?

[@JoshCartman]

The code doesn't work for me. I get a Run-time error 13. I have data from A1 to A28. I should be expecting A29 as output. I was originally trying to do this for a column, not a row. The xlUp code in this video confuses me, I would've thought it'd be xlDown, since you're going from top to bottom. Can you clarify? This is the code I tried using for the Column: MsgBox Worksheets("Sheet1").Cells(1, Columns).End(xlToRight).Offset(1).Address

[@spunjbom]

This is cool but how fast will it run with large data set?

[@TeachExcel]

It depends how many blanks and rows you have. But if that is an issue, just put it into an array and loop through the array to get the information you need and then translate that to the correct row.

[@adrisanme2010]

please how to modify the code so when running the cursor ends up in the first cell of the last empty raw, this is for the purpose of pasting data one empty raw at time , thanks

[@adrisanme2010]

how can i ask questions, thank you

[@rizzoliandisles9348]

will this work if i want to enter in the next adjacent cell

[@kieranmitchell5088]

When I try the first part to offset, I always get an error 438: object does not support this property or function. Do you know what I need to change?

[@alializadeh8195]

Thanx

[@iykhebe]

wow it does really help me to solve my problem to look for a new row that with empty cell. i wondering why only got 349 like ~ this is very helpful tutorial. i manage to cre8 a button that every click collect data from the form for record.

[@wardrich]

Is there a way to do this for a whole range? Say you want to write a block of data, but you're not sure how far down to go and there's a chance that the column you check is shorter than the other ones in the range? I mean, you could hard-code it, but I was thinking about making a more dynamic function - throw it a range, it gives you the next available line and returns it back as the left-most the the cell of the next available row in the range you sent. I've only been at this for a little bit, so if I figure it out, I'll report back lol [EDIT] Turns out Excel doesn't even inherently have this feature... it only pays attention to the leftmost column of your range... I'll have to figure out how to break a range down to individual columns, and bubble sort to find it that way lol This *should* do it, but be weary of merged cells - give it a range and it will parse it one cell at a time. If the cell isn't empty, it will go through a bubble sort to find out if it is the last row. I suppose that based on my FOR statements, I don't even need to bubble sort it Function FindWriteLine(checkSpace As Range) As Integer Dim ColRow() As String Dim CellSelect, lastRow As Range Dim columnCount, rowCount As Integer Set lastRow = Range("A1") ColRow() = Split(checkSpace.Address(rowabsolute:=False, columnabsolute:=False), ":") For rowCount = 1 To checkSpace.Rows.count For columnCount = 1 To checkSpace.Columns.count Set CellSelect = Range(ColRow(0)).Offset(rowCount - 1, columnCount - 1) 'CellSelect.Select If CellSelect.Value "" Then If rowCount > lastRow.Row Then Set lastRow = CellSelect End If End If Next columnCount Next rowCount FindWriteLine = lastRow.Row + 1 End Function

[@meganbevil5874]

This was great but quick question, what do you do if there is an IF statement in the cell that pulls from another tab? Technically there is a formula in the cell but its returning blank so that's where I would want the next True return to go and so on.

[@TeachExcel]

Check the .Formula property or .Value property of the range to get the desired result. For more help, you can upload a sample file and ask in our forum because I won't see when you reply to this comment. www.teachexcel.com/talk/microsoft-office?src=yt_comment_reply

[@GhostRider-mz1hl]

When following your tutorial I got an error code run time "9": Subscript out of range (WorkSheets("Sheet1").Range("AB18").Select), then I modified the code to this (Activesheet. Range ("AB18").Select) which partially worked, it pasted the values at AB18, then I got another run time error "9": Subscript out of range (Set nextEmptyCell = WorkSheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1) ) , I then modified the code to this Set nextEmptyCell = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1) and got a different run time error code (438) Object doesn't support this property or method ' 'Do Until WorksheetFunction.CountAB18(nextEmptyCell) = 0. The last TWO MODIFIED EXAMPLES only partially works up to the point where it pastes the values in AB18 then I keep getting run time error messages. The object of the exercise is to copy data from one workbook, then open another workbook and paste the values in AB18, then find the next blank cell in AB18 ready for the next entry, also I have a clear button set up in the destination workbook AB18 so the end user at anytime can clear the data in that area, so in theory the vba coding will start back at AB18 and so on. Please note both my workbooks are completely formatted with some conditional formatting especially in the paste area ect.. I am using Microsoft office 365 subscription, Windows 10 upgrade approximately 6 years ago, I look forward to hearing from you asap with a solution. With kind regards Ghost Rider, happy new year.

[@rosscoleman7778]

can anyone tell me how to copy a format from a range all the way until a bottom row that changes every time? this is helpful but doesn't drive it home.

[@GhostRider-mz1hl]

I have tried your vba code to find the next empty cell on my workbooks but! it does not work, because my workbooks I am working on all have a combination of formatted and conditional formatting in my cells as well as I have a clear button set up to clear the contents in the range, also the copied area (range) in my workbook/sheet has formulas built in the cells, the paste option in my vba code is PasteSpecial xlPasteValues, I am using microsoft office 365 subscription, so please at your earliest convenience let me know the solution, thank you, happy new year. Ps forgot to mention the error message I get is Application-defined or object-defined error or Subscript out of range (Error 9) here is the vba code i am using WorkSheets("Sheet1").Range("AB18").End(xlDown).Offset(1).Select Selection.PasteSpecial xlPasteValues

[@schowdhury6733]

How to do it in Next Empty COLUMN..????

[@GhostRider-mz1hl]

Oh forgot to mention in my previous 2 comments I did not use EntireRow in my coding as it was not required also I have other data ect...

[@redhaakhund1271]

👍👍👍👍👍🥇

[@wasio44]

Hello! Many thanks for the tutorial. it doesn't work for me either, but i don't understand. I don't really want an empty row, but a row with counta = 6 for specific reasons. and got a 1004 Error. It works with counta = 0 but not with counta = 6. any idea? " Sub NextEmptyRow() Dim nextEmptyCell As Range Set nextEmptyCell = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Offset(1) 'MsgBox WorksheetFunction.CountA(nextEmptyCell.EntireRow) = 6 Do Until WorksheetFunction.CountA(nextEmptyCell.EntireRow) = 6 Set nextEmptyCell = nextEmptyCell.Offset(1) Loop MsgBox nextEmptyCell.Address "

[@osoriomatucurane9511]

Awesome tutorial addressing the validation of the last row. Sub LastEntireRowEmpty() Dim lastRange As Range Set lastRange = wstSales.Cells(Rows.Count, 1).End(xlUp).Offset(1) MsgBox " last cell address could be " & lastRange.Address ''''''''' ====== Check if the entire row is completely empty ======= Dim LastEntireRowCount As Integer LastEntireRowCount = Application.WorksheetFunction.CountA(lastRange.EntireRow) MsgBox "what is the total count of non blank cells in the entire row? "_ & LastEntireRowCount If LastEntireRowCount 0 Then Do Until Application.WorksheetFunction.CountA(lastRange.EntireRow) = 0 Set lastRange = lastRange.Offset(1) MsgBox lastRange.Address MsgBox Application.WorksheetFunction.CountA(lastRange.EntireRow) Loop End If MsgBox "Well Done, Good to go! The last cell with entire empty row is: " & lastRange.Address End Sub