Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Jumping to the Start of the Next Data Entry Row.

Jumping to the Start of the Next Data Entry Row

by Allen Wyatt
(last updated July 12, 2014)

13

Do you need to always jump to the first cell right after all the data you've already put in your worksheet? For instance, if you have a worksheet that contains data in A1:G251, do you ever need to jump to cell A252 so that you can start entering data?

Moving to the first cell in row 252 is easy, provided there is data in all the cells in A1:A251. But if there can be empty cells in column A, then jumping to A252 can be a bit more difficult. In that case, you might be interested in a macro that makes jumping to the first cell of the empty row after your data quite easy:

Sub FindFirstCellNextRow()
    Dim x As Integer
    x = ActiveSheet.UsedRange.Rows.Count
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.EntireRow.Cells(1, 1).Offset(1, 0).Activate
End Sub

The first two lines effectively recompute the "last cell" in the worksheet and then the next two lines select that cell and jump to the cell in column A that is one row down.

Assign the macro to a keyboard shortcut, and you'll always be just one keystroke away from jumping to the first truly empty row in the worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6197) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Jumping to the Start of the Next Data Entry Row.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Exploded Pie Chart Sections

Want to change the groupings used by Excel when it creates pie charts? Your options are limited, as you learn in this tip.

Discover More

Counting Consecutive Negative Numbers

If you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...

Discover More

Resetting All Shortcut Keys

At some point you might want to wipe out all the custom shortcut keys you've created in Word. This is easy to do by following ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (RIBBON)

Recording a Macro

One of the most common ways of creating macros is to use Excel's macro recorder. This tip shows how easy it is to use the ...

Discover More

Self-Aware Macros

Sometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can ...

Discover More

Aborting a Macro and Retaining Control

If you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 6?

2015-10-25 06:29:01

Willy Vanhaelen

@Geert,

You don't need a macro for this. Just place the cell pointer in column A and press [End][Down][Down.]


2015-10-24 08:44:18

geert

Good afternoon,
the macro is doing his best but I have a problem because I have a sheet with not empty cells in colums b, c,...
I just want he takes me to the next free cell in column a
thanks


2015-03-25 00:48:25

Jeremy Taylor

Good afternoon,
I am having difficulty achieving this.
I have a 'table' which is A3:I953 (Including the Header row)

What the above macro is doing is taking me to A954 (which is the first empty row after the table)

What I want it to do is take me to the first empty cell within the table which is currently A464


2014-07-13 13:22:24

Willy Vanhaelen

@Micky,
I agree that when part of a bigger macro your solution can be an excellent choice.

My macro is intended as standalone for general use with any table in any place. So it must know which table. Hence the condition that the cell pointer be somewhere in the table when the macro is run.


2014-07-12 09:49:40

Michael (Micky) Avidan

@Willy,
1) No, I'm not kidding.
2) Take a look at the linked picture and tell me if this looks other than ridiculous !?
*** Row 4 may be a common situation, but 7, does NOT !
3) Your suggestion forces the user - as you stated - to select a cell within(!) the table.
4) The use of finding the last row number is usually PART of a large Macro therefore it does not seem logic to force the user to select a cell, within the table, while the macro is running.
5) In general - I'm against the use of the 'Select' command because it slows down the macro (even if you declare: Application.Screenupdating = False
6) I do hope we are not dragged into a Infertile discussion.
http://jpg.co.il/download/53c134f85b235.png
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-07-12 07:55:36

Willy Vanhaelen

I looked at your picture... you are kidding right? That has nothing to do with a table, it's just some scattered data. Indeed the tip specifies that some cells may be empty (not entire rows). So what happens with your solution if cell B17 is empty?

I must admit that in my comment of 9th July I should have mentioned "and your table doesn't have empty rows", but that's so evident because many Excel functions rely on that, such as Filter and Sort A to Z (Z to A) in Data or the Sum function (Alt+ =).

If my macro is put in the personal Workbook it will work with any table, in any sheet of any workbook whether or not the first column is A. Even if the sheet contains several tables it will work with each of them. The only condition: the cell pointer must be somewhere in the table and the table may not have empty rows.

To be complete: if the last row of the table is row 1048576, my macro will crash :-)


2014-07-11 10:06:27

Michael (Micky) Avidan

@Willy,
Taking into consideration that I manage my Workbooks and you, yours - one should know where his data lay.
In my suggestion one has only to change the column number.
(I usually take into consideration that Excel users do understand what they are doing and I'm against any "BLIND COPYING" of Formulas/Macros unless been understood).
Take a look at the linked picture and please tell me how ANY of your suggestions selects cell B18.
Please remeber that the above tip addresses, very clearly, a situation where some cells were left empty - so the only way to resolve this task is the "End-towards-up" approach - or you still have some "Hidden ideas in your sleeve" and if so I'll appreciate if you'll lighten my eyes with.
http://jpg.co.il/download/53bfea7e019ac.png
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-07-11 09:20:29

Glenn Case

I find that an easy way to enter data into a table is to set Excel to move the cursor to the right on Enter, and then preselect the table area for data entry. Say I have dat to be entered into C2:G10. I preselect that area, enter the value for C2, and when I hit Enter, it moves to D2, and so on. After entering the data for G2, when I hit Enter, the cursor automatically moves to C3, and so on through the table.

If I don't set the cursor to move right, then the cursor will move down after Enter, and after all data is entered for the first column, the cursor will automatically move up to the top of the next column, ans so forth.


2014-07-10 14:10:12

Willy Vanhaelen

@Micky
Nice approach, assuming the first column of the table is column A but if not, your solution will fail, mine still works.


2014-07-10 10:50:05

Bryan

D'oh! I got distracted by the fact that I had an extra blank row as part of the Used Range... I totally know that Ctrl+End won't get you to the next unused row...

The CurrentRegion approach will probably work in the most cases (not ALL cases, but I don't think there is one solution that will always work). For me, it seems strange to have any sort of data entry grid where none of the columns are mandatory. My first column is almost exclusively some sort of identifier for the row, so it doesn't make sense to leave it blank; in that case, Home, Ctrl+Down, then Down Arrow are my biggest friends!


2014-07-10 10:47:20

Michael (Micky) Avidan

To my opinion the best, shortest and accurate way is:

Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-07-09 09:44:21

Willy Vanhaelen

Ctrl+End will not give you the first empty row but the last used one. So to get the same result as with the macro you must follow it by Home AND Down Arrow key.

Both the macro and the keyboard shortcut rely on xlLastCell but that value refers to the utmost position of the current session but doesn't necessarily reflects the actual situation.

Here is a macro that is fool proof provided your cell pointer is somewhere in the table:

Sub FindFirstCellNextRow()
With Selection.CurrentRegion
ActiveSheet.Cells(.Row + .Rows.Count, .Column).Select
End With
End Sub


2014-07-08 08:14:00

Bryan

They keyboard shortcut equivalent to your code is Ctrl+End followed by Home.

If you choose to use the macro anyway, the first two lines do nothing and can be removed (this is pretty easy to see even if you don't read code: the variable x is set and then never referenced later in the code). The code could be rewritten a little better as well, but why bother when the keyboard shortcut does the same thing?

Note that this trick will only work if there is nothing below your data grid (and there really shouldn't be for proper data usage). If you have blank rows that Excel thinks are part of your used range, simply highlight the whole row (Shift + Space), delete them (Ctrl + - [minus]), and save the workbook (you may have to close and open the workbook, depending on the version of Excel). Now Ctrl+End then Home will give you the next empty row.


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share