Find the Next Blank Row with VBA Macros in Excel

Add to Favorites
Author: | Edits: don

Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks that are between your data but not at the very end of it.

Look at this example:


We want the VBA to locate cell C7 and NOT cell C5.

The VBA to do this is quite simple:

Sub Find_Next_Empty_Row()

'selects the next empty row
'ignores blanks inbetween the data set

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

End Sub

There are two comments in there and only one actual line of code:

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

This is where the magic happens and now Ill explain it.

The A is the column where we want to get the next usable blank cell.  So, if we wanted to check for the next cell in column D then you change the A to a D.

The Rows.Count part just counts how many rows are in Excel and adds them there so that it will not miss any rows when checking for blank ones.  This is important since the row count in Excel changed from version 2003 to 2007, and so this makes the code a bit more versatile.

The End(xlUp) part tells Excel to return the first cell with data that it finds when lookup up the worksheet starting at the last row in the worksheet.

So far, this will find the last cell that has data in it in the column, but we need to get the very next, or empty, cell and this is what Offset(1) does for us.  The 1 tells Offset how many rows it should offset and since we want the very next row, 1 works for us.

After that, we can do whatever we want with this cell.  At this point, Excel knows where we are talking about.  In this example, I used a simple Select to select the empty cell so you can verify that the code works but, like I said, you can do anything here; you dont have to actually select the cell first.

This is a very versatile piece of code and it should help save you a lot of time.

Make sure to download the Excel file for this tutorial so you can see the macro in action and play around with it.

I hope this was helpful! :)

Downloadable Files: Excel File 1, Excel File 2

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Do Something Every so Many Rows with a Macro in Excel
Tutorial: How to have a macro do something on a set interval of rows; for instance, input a value ev...
Guide to Creating Charts with a Macro in Excel
Tutorial: How to add, edit, and position charts in Excel using VBA. This tutorial covers what to do ...
Input Form to Get Data and Store it in Another Tab in Excel
Tutorial: How to make a user input form in Excel and have the data stored on another worksheet at th...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
Tutorial: How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of W...
What is a Macro in Excel?
Tutorial: This is the first step to learning about Macros for Excel and how to use them.  What is a...
The Best Tips for Recording Macros in Excel
Tutorial: This is for the beginner who wants to start learning about how to record macros in Excel ...