select first empty cell in a range
 

Let's say I have a range of cells (A5:D15). I would like a VBA code that
would start at A5 and go down from A5 to A15, then start at B5 and progress
to B15, then C5 - C15 etc. until it finds the first empty cell.

Please help.
--
Thanks
Shawn

select first empty cell in a range
 

This will loop through the cells in a the range going A5, B5, C5, D5,
A6, B6 etc until it finds an empty cell.

Sub lprng()
Dim cell As Range
For Each cell In Range("A5:D15")
If cell.Value = Empty Then
Debug.Print cell.Address
Exit For
End If
Next cell
End Sub

Or if you wanted to perform some operation on each cell in the range then:

Sub lprng()
Dim cell As Range
For Each cell In Range("A5:D15")
cell.Font.Bold = True
Next cell
End Sub

Hope this helps
Rowan

Shawn wrote:
Let's say I have a range of cells (A5:D15). I would like a VBA code that
would start at A5 and go down from A5 to A15, then start at B5 and progress
to B15, then C5 - C15 etc. until it finds the first empty cell.

Please help.

select first empty cell in a range
 

Hi Shawn,

Try:
'==================
Public Sub Tester()
Dim WB As Workbook
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = ActiveSheet '<<========== CHANGE

Dim rng As Range

Set rng = SH.Range("A5:D15")
Dim FirstBlank As Range
Dim i As Long

For i = 1 To rng.Columns.Count
On Error Resume Next 'In case no empty cells found!
Set FirstBlank = rng.Columns(i).SpecialCells(xlBlanks)(1)
On Error GoTo 0
If Not FirstBlank Is Nothing Then
'First empty cell found
'Do something e.g.:
MsgBox FirstBlank.Address
Exit For
End If
Next i
If FirstBlank Is Nothing Then
'No empty cells found in designated range!
'Do something else, e.g.
MsgBox "No empty cells found in " _
& rng.Address(0, 0, external:=True)

End If
End Sub
'<<==================


---
Regards,
Norman



"Shawn" wrote in message
...
Let's say I have a range of cells (A5:D15). I would like a VBA code that
would start at A5 and go down from A5 to A15, then start at B5 and
progress
to B15, then C5 - C15 etc. until it finds the first empty cell.

Please help.
--
Thanks
Shawn