Discussion on:

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

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?

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.

@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

...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 ... ?

"...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!

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

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

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

.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)

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

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

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.

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

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.

Cells(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count, 1).Activate

Changed it just a little to accommodate initial blank rows.

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