Next Empty Row Trick in Excel VBA & Macros

Please share this video if you liked it :)

A simple way to find the next completely empty row in Excel using VBA/Macros, even if some columns are missing values.

This tutorial combines the basic code for going to the end of a data set with additional code that verifies that a row is empty and, if it is not, it will then move down the worksheet until it finds the next truly empty row.

The system I show you here allows you to ensure that you find the next empty row, even if your data set is non-standard or full of empty cells.

Excel Courses:

#msexcel #tutorials #msoffice

[tuantrinhngoc]

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

[rayvanderheiden]

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.

[camis]

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!

[Red]

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

[abbywilkins]

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?

[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

[KeyserTheRedBeard]

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

[darrylmorgan]

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

[triesjeflagg]

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.

[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 = - 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

[kevincoleman]

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

[spunjbom]

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

[meganbevil]

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.

[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,

[rizzoliandisles]

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

[GhostRider-mzhl]

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

[kieranmitchell]

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?

[adrisanme]

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