Macros in excel

[plugger]

how to find first empty cell in a selected column ie A12:A20 with a macro

 

[Ryan H]

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

 

[Dave Ramage]

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

 

[mcescher]

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

 

[Rick Rothstein]

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

Try this code (return the row instead of the address if that is what you
want)...

On Error Resume Next
BlankCellAddress = Selection.SpecialCells(xlCellTypeBlanks)(1).Address
On Error GoTo 0

 

[Rick Rothstein]

I should have mentioned that the empty string would be returned by my posted
code if there were no blanks in the selected range. If you change the
variable to BlankCellRow (and Dim it as Long) and return the Row property to
the variable, then BlankCellRow would be set to zero if there were no blanks
in the selected range.