Find first empty row in Excel and select

Question

I tried adapting this post: Error in finding last used cell in VBA to my needs but couldn't quite get it to work.

I'm pasting data into a new worksheet and then want to select the first empty row after data. Currently what's happening is the data is pasted and then the very first row in the sheet is selected. See code below. Any thoughts?

'runs when user enters data
If Target.Cells.Count = 1 And _
   Not Application.Intersect(Target, [I3:I10000]) Is Nothing Then

    Application.EnableEvents = False
    'User inputs type of event
    Archive = InputBox("Was this event a Win, Loss, or Close? (Please input Win/Loss/Close)")
With Target

    If Archive = "Win" Then
    'all data to transfer is selected and cut
            .EntireRow.Select
            Selection.Cut
    'the receiving sheet is selected and data is pasted to the selected cell
        Sheets("Win").Select
            ActiveSheet.Paste
    'the selection on the sheet the data was cut from is deleted
        Sheets("Begin").Select
            Selection.Delete
    'this is the issue I'm having - I want to select the row below the row I just copied into.
        Sheets("Win").Select
           lastRow = Range("C" & .Rows.Count).End(xlUp).Row
           ActiveSheet.Range("C" & lastRow & ":C" & lastRow).EntireRow.Select
       Sheets("Begin").Select

paul bica · Accepted Answer · 2015-06-29T23:59:26.173

2

Try replacing this:

'this is the issue I'm having - I want to select the row below the row I just copied into.
Sheets("Win").Select
    lastRow = Range("C" & .Rows.Count).End(xlUp).Row
    ActiveSheet.Range("C" & lastRow & ":C" & lastRow).EntireRow.Select

with this:

With Sheets("Win")
    lastRow = .Range("C" & .Rows.Count).End(xlUp).Row
    .Cells(lastRow + 1, 1).EntireRow.Select
End With

edited Jun 29 '15 at 23:59

answered Jun 29 '15 at 22:31

paul bica

10,557
4
23
42

score 1 · Answer 2 · answered Jun 30 '15 at 00:14

Just to add to the existing answer. You can avoid doing so much selection by using a construction more like this:

On Error GoTo problem

Dim Archive As String

If (Target.Cells.Count = 1) And _
   Not (Excel.Application.Intersect(Target, [I3:I10000]) Is Nothing) Then

    Excel.Application.EnableEvents = False
    'User inputs type of event
    Archive = InputBox("Was this event a Win, Loss, or Close? (Please input Win/Loss/Close)")

    With Target

        '>>>> good idea to defend against users entering "win" instead of "Win"
        If (LCase(Archive) = "win") Then

            '>>>> find the last row in Win sheet at the beginning
            With Sheets("Win")
               lr = .Range("C" & .Rows.Count).End(Excel.xlUp).Row
            End With

            '>>>> as you are cutting there should be no need to do any subsequent deletion or clearcontents 
            .EntireRow.Cut Sheets("Win").Rows(lr + 1)

        End If

    End With
End If

problem:
Excel.Application.EnableEvents = True

Find first empty row in Excel and select

2 Answers2