Find and Select the First Blank Cell in a Column VBA

Sometimes, You may need to find and select the first blank cell or last blank cell in a column, these macros can help you.

Find and Select the First Blank Cell in Column A

Sub Macro1()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
End Sub

or

Sub Macro2()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells
         If Len(cell) = 0 Then cell.Select: Exit For
    Next cell
End Sub

First blank cell: before selection

first blank cell before selection

First blank cell: after selection
first blank cell after selection

Find and Select the Last Blank Cell in Column A

Sub Macro3()
'Step 1: Declare Your Variables.
 Dim LastRow As Long
'Step 2: Capture the last used row number.
 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Step 3: Select the next row down
 Cells(LastRow, 1).Offset(1, 0).Select
End Sub

or

Sub Macro4()
'Step 1:  Declare Your Variables.
    Dim LastBlankRow As Long
'Step 2:  Capture the last used row number.
    LastBlankRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
'Step 3:  Select the next row down
    Cells(LastBlankRow, 1).Select
End Sub

Note: Some of the most common ways of finding last row which are highly unreliable and hence should never be used:

  1. UsedRange
  2. xlDown
  3. CountA

Why we use Rows.Count not 65536?

This question is a classic scenario where the code will fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.

How to use it

To use this macro, you can copy and paste it into a standard module:

  1. Activate the Visual Basic Editor by pressing ALT F11.
  2. Right-click the project/workbook name in the Project window.
  3. Choose Insert -> Module.
    Insert Module
  4. Type or paste the code in the newly created module.

Leave a Reply

Your email address will not be published. Required fields are marked *

18 comments
  1. William
    William

    For “Find and Select the First Blank Cell in Column A”–Thank you very much! It’s a tremendous help!

  2. Venus
    Venus

    Thanks for sharing the code. I’m new here, would like to know how to include formula after selecting the next empty cell in VB?

  3. John Mourning
    John Mourning

    The first example produces a compile error – cell variable not defined. Am I missing something?

  4. Jamie
    Jamie

    Any idea how to find the next blank cell in a specific column after input data? So, cell C is the column, but its in row 710. The imported data gets added, I am trying to get the data to import at the next blank line…

  5. James Greaves
    James Greaves

    ThisRow = Sheets(“Sheet1”).Columns(1).Find(“”).Row

  6. Edy
    Edy

    I’m wondering if there is any reason to using a “For” loop instead of using the range End property End(xlDown) or End(xlUp) ?

  7. Mark
    Mark

    I got that too! Gives this site a lot of credibility, huh?

    I added “Dim cell as range” and it worked as described.