CODEWITHSUNDEEP
excelvba
user3047395
user3047395

Reputation: 63

Go to first empty cell after selected cell

I am trying to implement a code, where if you click a certain cell; you go to the first empty cell in a certain column.

Now I have this code:

If Selection.Count = 1 Then
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Columns("E").Find(vbNullString, Cells(Rows.Count, "E")).Select
    End If
End If

But there is a problem with this code: I want it to start checking the first empty cell; starting at row 3. How do I do this?

Edit1:

I have made some adjustments to the code to fit my needs (for practice and aesthetics);

Dim lastCell As Range
Set lastCell = Range("E:E").Find(vbNullString, [E3], , , , xlNext)

lastCell.Interior.Color = RGB(100, 200, 100)
lastCell.Offset(0, -3) = "Last Cell -->"
lastCell.Offset(0, -3).Interior.Color = RGB(0, 110, 250)
lastCell.Offset(0, -3).Font.Color = vbWhite

If Not Intersect(Target, [B2]) Is Nothing Then
    lastCell.Select

Side Note The reason for Offset three columns to the right is because of the lay-out of the sheet :) I clear the formatting of the cell and the text somewhere else if lastCell is changed. So if anyone is interested, let me know.

Upvotes: 2

Views: 843

Answers (2)

L42
L42

Reputation: 19727

You can re-write your code like this, just by supplying SearchDirection argument.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Columns("E").Find(vbNullString, Cells(Rows.Count, "E") _
            , , , , xlPrevious).Select
    End If
End If
End Sub

Or you can try this one:

Edit1: For brettdj :)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Goto errhandler
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
    If Not Intersect(Target, [B2]) Is Nothing Then _
        Range("E:E").Find(vbNullString, [E3], , , , xlNext).Select
End If
continue:
Application.EnableEvents = True

Exit Sub
errhandler:
MsgBox Err.Description
Resume continue

End Sub

Both code works the same way except if there are blank cells in between E3:E(x).
Your revise code finds the first empty cell in Column E with reference to the last non empty cell.
The next code literally finds the first empty cell from E3. Don't know which is really what you need.

Side Notes:

Columns("E") is the same as Range("E:E").
Why use Range("E:E") then? Well, Intellisense kicks in with Range and not with Columns.
So I prefer using Range so you can see all the available arguments of .Find method.

Upvotes: 3

dcromley
dcromley

Reputation: 1410

This is what I would do:

  Dim maxrows&, iRow&, iCol&, zcell As Range
  maxrows = Excel.Rows.Count
  If Selection.Count = 1 Then
    iRow = Target.Row
    iCol = Target.Column
    Set zcell = Range(Cells(3, iCol), Cells(maxrows, iCol)).Find(vbNullString)
    zcell.Select
  End If

Upvotes: 0

Related Questions