Software

Office challenge: What VBA formula would you use to select the first empty cell in an Excel spreadsheet?

Learn the solution to last week's PowerPoint selection challenge and test your Excel skills with this week's VBA challenge.

Entering new data into an Excel spreadsheet is a common task. There are many steps to automating data entry and they depend a lot on the specifics of the task at hand. However, one of the first things you'll want to do is select the first empty cell in the spreadsheet. What VBA formula, function, or macro would you use to select the first empty cell in a spreadsheet? This challenge is wide open as there's certainly more than one way to approach this solution.

Last week we asked…

How can you quickly select the contents of a PowerPoint text box? This has been a fun challenge. Your responses were creative, but I have one more than no one mentioned—press [Esc]. After adding a text box to a slide and typing a label, clicking a formatting option, such as Bold, Italics, and so on, has no effect--nothing happens. If you press [Esc] and click a formatting option, PowerPoint applies the format!

When you press [Esc], you probably won't see an obvious indication that PowerPoint has selected the text, but don't let that trick you into thinking that this simple technique doesn't work. The control's border does change, but it's subtle and you might miss it, so watch for it.

This technique also works with existing controls—it isn't just for new ones. Simply select a control, press [Esc] and start formatting.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

21 comments
rgray
rgray

This will accurately find the last used cell. Then you would need to determine the offset (row and columns) that you are looking for. Sub getlastUsedCell() Dim LastRow As Integer Dim LastCell As String Dim pos As Integer Dim lastUsedCell As String LastRow = ActiveSheet.UsedRange.Rows.Count LastCell = Range("A" & LastRow).CurrentRegion.Address pos = InStr(LastCell, ":") + 1 lastUsedCell = Mid(LastCell, pos) End Sub

RU7
RU7

Cells(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count, 1).Activate Changed it just a little to accommodate initial blank rows.

mwb78
mwb78

Maybe you posted this question before the reply was posted to the PowerPoint question, but PowerPoint Rain Man presented exactly the solution of using ESC, then formatting. This question and his reply are both dated November 16, so maybe you crossed in cyberspace. Keep up the good work. I often get a lot of your challenges but I am always finding them a week late.

fledis
fledis

I'd rather press and then appropriate arrow key (arrow key twice assuming that the first active cell wasn't empty).

jbenton
jbenton

I'd define the name "Next" for the leftmost cell of the first blank line. "Next" is also the destination of a hyperlinked cell (though you could put Range(Next).Select in a VBA module) in the Define Name dialogue box the name "Next" refers to =INDIRECT("A"&MAX(ROW($A$1:$Z$999)*NOT(ISBLANK($A$1:$Z$999)))+1) you could even use this in conditional formatting to highlight the next cell to use

biaogewang
biaogewang

.SpecialCells(xlCellTypeLastCell) won't be updated until the file is save so it's not as reliable. Also it includes formatting changes as last used cell. i used this to find the first empty cell in column A: Set NextCellInCol = Range("A1048576").End(xlup).Offset(1) or for Excel 2003: Set NextCellInCol = Range("A65536").End(xlup).Offset(1) or if there may be blank cells in column A, then i will use Find to find the last "used" row. LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Set CellforEntry = Cells(LastRow+1, 1)

agency
agency

Sub emptycell() Range("A1").Select ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(1, 0).Range("A1").Select Selection.End(xlToLeft).Select End Sub

agFinder
agFinder

Sub selectcell() If MsgBox("Select the column (press yes) or row (press no)?", vbYesNo) = vbYes Then Cells(ActiveCell.CurrentRegion.Rows.Count + 1, ActiveCell.Column).Select Else Cells(ActiveCell.Row, ActiveCell.CurrentRegion.Columns.Count + 1).Select End If End Sub

amasa
amasa

How about this? Sub EmptyCell() Dim A, B As String Dim I As Integer A = "A" For I = 1 To 100 B = A & CStr(I) Range(B).Select If IsEmpty(Selection) Then Exit Sub End If Next I End Sub

yogi_john
yogi_john

Since you're talking about data entry, I'll assume the "first empty cell in the spreadsheet" refers to the first column of the row below the last row. For that: Sub NextDataEntryCell() ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(1, 0).Select Selection.End(xlToLeft).Select End Sub On the other hand, if you're looking for the first empty cell in column A: Sub FirstEmptyCellInColA() Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select End Sub Of course, if you have headers with some blanks prior to the data, then replace A1 with the appropriate cell or reference name.

viveka
viveka

clicking esc on a selected text box in an edit mode is the same as the function key "F2" that toggle between edit and selecting entire content. PS: does "send keys" still exits?

RU7
RU7

I know it is a stretch, but if there are more blank rows above the used cells than there are used rows, I think this will return A1. For example, if there are 4 rows of data starting in cell A6, this will return $A$4.

ssharkins
ssharkins

Thank you for acknowledging Rain Man's response. I usually check on Monday evenings, so you're right -- I probably just missed that post!

RU7
RU7

I have a fuel usage file for all my vehicles, one vehicle per sheet. I also have numerous graphs such as miles per gallon, per day, $ per gallon, mgp vs miles per day ... Some of them show data for a single vehicle and some show data for all the vehicles. Calculations for these graphs are in each row of data. I have a macro assigned to an object on each sheet that finds the last used row, copies it, then pastes it to the first unused row. It then updates the data range of all the appropriate graphs to include the new row.

Histrion2
Histrion2

...could you be more specific as to what you mean by "first empty cell?" Do you mean the first empty cell in column A, or the first empty cell in row 1, or the first empty cell within a particular number of rows & columns, or on a diagonal, or ... ?

aikimark
aikimark

@yogi_john I agree that there are several interpretations to this Susan Harkins question. We must make some assumptions to properly qualify our answer(s). I reduced your code to a single line: Option Explicit Public Sub NextDataEntryCell() ActiveCell.SpecialCells(xlLastCell).Offset(1, 0).End(xlToLeft).Select End Sub

Slayer_
Slayer_

You can use vba.sendkeys, or sendkeys (which I am not sure if it will use excel's sendkeys or vba's, but they are wrappers for the same command)

fledis
fledis

Oh yes. In this case writing a macro makes sense. I mean- not just to find free cell but also to perform numerous actions.

ssharkins
ssharkins

"...could you be more specific as to what you mean by "first empty cell?" Do you mean the first empty cell in column A, or the first empty cell in row 1, or the first empty cell within a particular number of rows & columns, or on a diagonal, or ... ?" Yes, let's assume column A for the most part. However, if one of you want to adapt a solution to find the first empty cell in a specific column, that would be fine too!

RU7
RU7

should be considered in that light. Code isn't usually written for just doing the subject function, but for being used in a larger, more complex and repetitive event. Unless your only objective is learning VBA, it would be of little value to write code to do something that can be done with a couple key strokes.

p@re
p@re

The one I use more often: Dim EmptyCell as Long EmptyCell = Cells(Rows.Count, "A").End(xlUp).row (Change "A" for the column you want to search) (Add 1 for the empty cell number) Another one that gets the last line wathever which column has the most values entered. EmptyCell = Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row (One issue is when the sheet is empty... It returns 0...)