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

Printing Copy Numbers

Copy 1, Copy 2, Copy 3... Do you want to mark your printouts so that they are numbered? Here's how you can do it.

Discover More

Running Macros on Hidden Worksheets

Excel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...

Discover More

Contingent Validation Lists

Data validation can be used to create lists of choices for entry into a particular cell. Using the techniques in this tip you ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Extracting Proper Words

If you've got a list of potential words, and you want to know which of those potential words are real, you'll appreciate the ...

Discover More

Making Sure Cells are Filled In before Saving

When creating a workbook that will be used by others, you may wish to ensure that the user fills in some cells before saving. ...

Discover More

Exiting a For ... Next Loop Early

If you use For ... Next loops in your macros, make sure you give a way to jump out of the loop early. That way you can limit ...

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

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. Maximum image size is 6Mpixels. 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 two more than 4?

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.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

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.