Finding Next Empty Cell in Column
 

Hope this question hasn't come up to often. I can't find where it was asked
before so here goes.

I would like to find the next empty cell below my last entry. What VBA code
would I use to accomplish this.

Steve

Finding Next Empty Cell in Column
 

Hi Steve,

There may be a number of ways to accomplish this but I'll send you a couple
of solutions that immediately occur to me. I'm first going to assume that
you're making cell entries in a single column (if I'm wrong, please let me
know). Let's say that your data entries begin in cell A6. You want your
code to locate the next data entry cell in column A (the first blank cell
below A6). One way to do this is:

Method 1: Find the last possible data entry cell in the column then do and
"End Up"

Sub FindNextCell()
'Locate the next data entry cell in data entry column A
If Range("A65536").Value = "" Then
Range("A65536").Select
Selection.End(xlUp).Select
Else
MsgBox "You have filled the data entry column"
End If
End Sub

Method 2: Loop through the data entry cells until you find the first blank

Sub FindNextCell()
'Locate the next data entry cell in data entry column A
Dim FirstCell As String
Dim i As Integer
FirstCell = "A6"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

"caldog" wrote:

Hope this question hasn't come up to often. I can't find where it was asked
before so here goes.

I would like to find the next empty cell below my last entry. What VBA code
would I use to accomplish this.

Steve

Finding Next Empty Cell in Column
 

simple:

ActiveCell.End(xlDown).Offset(1, 0).Select

or, if you want to make it complicated :-)

Range(ActiveCell, Cells(65536, ActiveCell.Column)) _
.SpecialCells(xlCellTypeBlanks).Select
ActiveCell.Selec

--
Pointles
-----------------------------------------------------------------------
Pointless's Profile: http://www.excelforum.com/member.php...fo&userid=3086
View this thread: http://www.excelforum.com/showthread.php?threadid=50804

Finding Next Empty Cell in Column
 

Couple of elaborating comments on Pointless's suggestions.

If you are sitting at the bottom of the UsedRange when you do this, both of
these methods will raise an error.
ActiveCell.End(xldown) would go to the last row in the column, then doing an
offset of 1 more row, raises the error

Specialcells will only look at the used range, so if you are at the bottom
of the usedrange, from a specialcells perspective, all the empty cells
below that are virtual and do not exist. This would result in an error.

Either could be corrected by making a check of the next cell below.

if isempty(activecell.Offset(1,0)) then
ActiveCell.Offset(1,0).Select
else
' one of Pointless's methods
end if

The above will fail if you are sitting on row 65536, but I would expect this
to be unlikely.
Another consideration with ActiveCell.End(xldown).Offset(1,0).Select is if
you are sitting in a blank cell with two or more contiguous cells that are
not empty below you, then This would select the second filled cell. Since
you said below my last entry, I assume this would not be the case.



--
Regards,
Tom Ogilvy




"Pointless" wrote
in message ...

simple:

ActiveCell.End(xlDown).Offset(1, 0).Select

or, if you want to make it complicated :-)

Range(ActiveCell, Cells(65536, ActiveCell.Column)) _
SpecialCells(xlCellTypeBlanks).Select
ActiveCell.Select


--
Pointless
------------------------------------------------------------------------
Pointless's Profile:
http://www.excelforum.com/member.php...o&userid=30862
View this thread: http://www.excelforum.com/showthread...hreadid=508042

Finding Next Empty Cell in Column
 

Thanks folks that is exactly what I was looking for.

Steve

"Pointless" wrote:


simple:

ActiveCell.End(xlDown).Offset(1, 0).Select

or, if you want to make it complicated :-)

Range(ActiveCell, Cells(65536, ActiveCell.Column)) _
.SpecialCells(xlCellTypeBlanks).Select
ActiveCell.Select


--
Pointless
------------------------------------------------------------------------
Pointless's Profile: http://www.excelforum.com/member.php...o&userid=30862
View this thread: http://www.excelforum.com/showthread...hreadid=508042