Prev: Cell content capture and release
Next: Fire Code after Preceding Events Run
From: plugger on 18 Feb 2010 10:25
how to find first empty cell in a selected column ie A12:A20 with a macro
From: Ryan H on 18 Feb 2010 11:01
This should do it. Hope this helps! If so, let me know, click "YES" below.

Sub FindFirstEmptyCell()
MsgBox Range("A12:A20").End(xlDown).Offset(1)
End Sub
--
Cheers,
Ryan


"plugger" wrote:

> how to find first empty cell in a selected column ie A12:A20 with a macro
From: Dave Ramage on 18 Feb 2010 12:07
You can use the End property of a range- this is the same as holding down
Ctrl + [Down/Up Arrow]. If you are sure that there is one continuous range of
non-blank cells in the column then it is more reliable to start at the bottom
of the column and search up:

Sub GetBlankCell_1()
Dim lRow As Long, lColToCheck As Long

'''find first empty row in column A
lColToCheck = 1 'Column A
'check last row in this column
If Cells(Rows.Count, lColToCheck).Formula > "" Then
'assume no empty cells in this column
lRow = Rows.Count
Else
lRow = Cells(Rows.Count, lColToCheck).End(xlUp).Row + 1
End If

'now do something with this value
Cells(lRow, lColToCheck).Select
End Sub

If it is better to start at the top of the column and search down, then use
this:

Sub GetBlankCell_1()
Dim lRow As Long, lColToCheck As Long

'''find first empty row in column A
lColToCheck = 1 'Column A
'check last row in this column
If Cells(Rows.Count, lColToCheck).Formula > "" Then
'assume no empty cells in this column
lRow = Rows.Count
Else
lRow = Cells(Rows.Count, lColToCheck).End(xlUp).Row + 1
End If

'now do something with this value
Cells(lRow, lColToCheck).Select
End Sub

In both examples, if the column is full then the last cell in the column is
selected.

Cheers,
Dave


"plugger" wrote:

> how to find first empty cell in a selected column ie A12:A20 with a macro
From: mcescher on 18 Feb 2010 12:09
On Feb 18, 9:25 am, plugger <plug...(a)discussions.microsoft.com> wrote:
> how to find first empty cell in a selected column ie A12:A20 with a macro

This will find the empty cell no matter what range you've selected.
Not just limited to A12:A20.

Sub FirstEmptyCell()
Dim rngTest As Range, rngCell As Range
Set rngTest = Application.Selection
For Each rngCell In rngTest
If IsEmpty(rngCell) Then
MsgBox rngCell.Address & " is the first empty cell"
Exit Sub
End If
Next
End Sub
From: Rick Rothstein on 18 Feb 2010 13:20
First off, since the cell will be empty, nothing will be displayed in the
MessageBox. I'm guessing you accidentally left off a reference to the
address property (.Address). However, your approach might not always produce
the correct result. For example, what do you get if all the cells in A12:A20
have entries in them *except* for A13 and a15?

--
Rick (MVP - Excel)


"Ryan H" <RyanH(a)discussions.microsoft.com> wrote in message
news:72F61672-0AA8-4A9D-A794-7C2B66E410CB(a)microsoft.com...
> This should do it. Hope this helps! If so, let me know, click "YES"
> below.
>
> Sub FindFirstEmptyCell()
> MsgBox Range("A12:A20").End(xlDown).Offset(1)
> End Sub
> --
> Cheers,
> Ryan
>
>
> "plugger" wrote:
>
>> how to find first empty cell in a selected column ie A12:A20 with a macro

 |  Next  |  Last
Pages: 1 2
Prev: Cell content capture and release
Next: Fire Code after Preceding Events Run