How to go to the First or Last Empty Row in Excel?

This article focusses about the evaluation of the first and last empty rows. To accomplish the provided task, it is better to use VBA code. VBA is an acronym for visual basic analysis. It is a Microsoft programming language used for office applications. It contains a coding script. Code can run like other front-end programming languages. Empty rows are those that do not contain any data but are available in the provided table structure. During this strategy user learn how to open the VBA editor, and steps to execute the defined module.

Examples to go to Evaluate the First or last Empty Row in the Excel Sheet

Example 1

Step 1

Consider the below given sample data, to understand the data processing easily:

Step 2

After that go to the “Developer” tab and go to the “Code” section, to select the option “Visual Basic”. Consider below depicted image screenshot for reference:

Step 3

This will open the below given editor:

Step 4

After that select “Sheet 1” and click on “Insert” Module. This will allow user to define module for required code segment.

Step 5

This will open a blank code area. Click on the editable area, and copy and paste the below given code data:

' name of defined module
Sub first_blank_cell()
'declare required variables
    Dim R As Range
    Dim m1 As Range
    On Error Resume Next
    'setting variable
    Set m1 = Application.Selection
    'setting variable
    Set m1 = Application.InputBox("Range", xTitleId, m1.Address, Type:=8)
    ' for each expression
    For Each R In m1
        ' if block
        If R.Value = "" Then
            ' display data on excel sheet
            MsgBox "No data value, in " & R.Address
       ' end of if block
        End If
    Next
' end of method definition module
End Sub

In the above code segment user is defining a module with name “first_blank_cell”.

Consider below given snapshot for code

[VBA is a syntax-based programming language. Please use proper syntax otherwise, code will display error.]

Step 6

After that from the command bar select “Run” button:

Step 7

After that click on run, and below given dialog box will appear on console:

Step 8

After that choose the table range as depicted below:

Step 9

Finally, click on “Ok”. This will display below given dialog box, and finally click on “OK” button.

Step 10

Now, let’s evaluate the last empty blank row. For this right click on “sheet1” option and click on “Insert”, and then click on “Module”.

Step 11

Type the below given code to appeared window editor:

' method definition
Sub last_empty_data()
    ' method body
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
' end of method
End Sub

Code editor will appear as given below:

Step 12

Click on below given “run” button, to obtain required output:

Step 13

This will automatically shift the cursor to last empty record within the excel sheet.

Conclusion

This article provides a simple illustration with an example by guiding the learner about the way of using VBA code so that user can easily track the first and last empty rows. After successful execution of all the steps, the user will be able to solve the provided question.