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

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

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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

I'm Trying to work through that same case of looking into the next empty cell in a specific column... in fact,
I have a user form and a set of 10 (Check Box-Label-Text Box-Text Box)... I'm trying to link them in a condition that states:
If a Checkbox is true
put its label caption in the first empty cell in column F of sheet3
put its 1st textbox value in the cell with the same row number but in column G of sheet3
put its 2nd textbox value in the cell with the same row number but in column H of sheet3

I have tried all methods listed below but without any result because my active cell is actually located in sheet1
(Userform gets activated if a certain condition works on any cell within a range of sheet1 and sheet3 is like a History sheet that stores the result)

So my guess is the "ActiveCell" term in all methods is the reason behind this not working
plus it is a "select" method at the end of it while I need it to be a "select and set value" method at the end of it

Example:

Please Login or Register  to view this content.

Any suggestions?

Welcome to the Forum GR15. Please review the rules below. Refer to Rule No. 2 about posting questions in the thread of another.

http://www.excelforum.com/forum-rule...rum-rules.html

Sorry For that John,

As a new member I sometimes forget some rules while posting

Anyways thanks for the notice, will make sure to follow it.

Thank you very much Paul.
I have been trying for weeks to find a way offset the first few cells to find the next blank cell.