FIND AND GO TO THE LAST BLANK CELL IN A CERTAIN COLUMN

[Alexandru Dan Mitache]

Hello All!

I have a worksheet with values in columns A:F, but not all the columns have equally filled rows.
I'd like to have a VBA to find and go to the last empty cell in column C, i.e. in cell C6 in my example.
Of course, the real worksheet has much more rows.
Thank you!

 

[Alexandru Dan Mitache]

I forgot to mention that it could happened to have in the apparently empty cells C6:C10 some blanks like "".

 

[Vogateer]

Does this do it?

Sub FindNextBlankCell()
    Dim blank_row As Long, blank_cell As Range
    blank_row = Sheets("Sheet1").Range("C1").End(xlDown).Row + 1
    Set blank_cell = Sheets("Sheet1").Cells(blank_row, 3)
    blank_cell.Select ' Do whatever you want with this cell
    End Sub

Adjust the "Sheet1" to the actual sheet name.

 

[MARK858]

If on the Activesheet try...

Sub Mitache()
 Columns(3).Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1).Select
End Sub

 

[MARK858]

Adjust the "Sheet1" to the actual sheet name.

Sheet1 has to be the Activesheet or your code will error (the code will also not treat the cells with "" as blank [post 2])

 

[Vogateer]

Sheet1 has to be the Activesheet or your code will error (the code will also not treat the cells with "" as blank [post 2])

You're right. I nearly never use .Select, so I put it in as a placeholder, but I should have selected the Sheet to make it actually work from other worksheets:

Sub FindNextBlankCell()
    Dim blank_row As Long, blank_cell As Range
    With Sheets("Sheet1")
        blank_row = .Range("C1").End(xlDown).Row + 1
        Set blank_cell = .Cells(blank_row, 3)
        .Select ' Only needed if you're actually using Select below
        blank_cell.Select ' Select is just an example that shows which cell was found.
    End With
    End Sub

If you don't use .Select, you should be able to do what you want with the cell without bothering with

 

[MARK858]

@Vogateer The other option is to use Application.Goto

Sub FindNextBlankCell2()
    Dim blank_row As Long, blank_cell As Range
    blank_row = Sheets("Sheet1").Range("C1").End(xlDown).Row + 1
    Set blank_cell = Sheets("Sheet1").Cells(blank_row, 3)
    Application.Goto blank_cell, True ' Do whatever you want with this cell
End Sub

There will still be the issue with the ""

 

[Vogateer]

Yes, my idea isn't going to work. Sorry I missed the ="" issue.

 

[MARK858]

No need to apologize

 

[Alexandru Dan Mitache]

Does this do it?

Sub FindNextBlankCell()
    Dim blank_row As Long, blank_cell As Range
    blank_row = Sheets("Sheet1").Range("C1").End(xlDown).Row + 1
    Set blank_cell = Sheets("Sheet1").Cells(blank_row, 3)
    blank_cell.Select ' Do whatever you want with this cell
    End Sub

Adjust the "Sheet1" to the actual sheet name.

It works, but if there are false blanks like "" in the cells below C5, the code sends to the last real empty cell, not to C6.