empty cells in ranges
 

I am building a macro which will populate a range but I need to know how
to goto the next empty cell in that range

Any suggestions, good people?


--
UniDave
------------------------------------------------------------------------
UniDave's Profile: http://www.excelforum.com/member.php...o&userid=16844
View this thread: http://www.excelforum.com/showthread...hreadid=320492

Hi UniDave,

Perhaps you could use something like:

Sub Tester03()
Dim rng As Range
Dim rng1 As Range
Dim rngArea As Range
Dim rCell As Range
Dim i As Long, j As Long

Set rng = Range("A1:D20") '<<===== CHANGE
On Error Resume Next 'In case there are no blanks!
Set rng1 = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

For Each rngArea In rng1
For j = 1 To rngArea.Columns.Count
For Each rCell In rngArea.Columns(j)
'Add your populating code, e.g:
i = i + 1
rCell = i * 2
Next rCell
Next j
Next rngArea
End Sub

If you need further assistance, it would be useful to know your population
criteria.

---
Regards,
Norman



"UniDave" wrote in message
...

I am building a macro which will populate a range but I need to know how
to goto the next empty cell in that range

Any suggestions, good people?


--
UniDave
------------------------------------------------------------------------
UniDave's Profile:
http://www.excelforum.com/member.php...o&userid=16844
View this thread: http://www.excelforum.com/showthread...hreadid=320492

Hi UniDave,

In case there are no blanks, better would be

Sub Tester03()
Dim rng As Range
Dim rng1 As Range
Dim rngArea As Range
Dim rCell As Range
Dim i As Long, j As Long

Set rng = Range("A1:D20") '<<===== CHANGE
On Error Resume Next 'In case there are no blanks!
Set rng1 = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng1 Is Nothing Then
For Each rngArea In rng1
For j = 1 To rngArea.Columns.Count
For Each rCell In rngArea.Columns(j)
'Add your populating code, e.g:
i = i + 1
rCell = i * 2
Next rCell
Next j
Next rngArea
Else
MsgBox "There are no blank cells!"
End If
End Sub

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi UniDave,

Perhaps you could use something like:

Sub Tester03()
Dim rng As Range
Dim rng1 As Range
Dim rngArea As Range
Dim rCell As Range
Dim i As Long, j As Long

Set rng = Range("A1:D20") '<<===== CHANGE
On Error Resume Next 'In case there are no blanks!
Set rng1 = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

For Each rngArea In rng1
For j = 1 To rngArea.Columns.Count
For Each rCell In rngArea.Columns(j)
'Add your populating code, e.g:
i = i + 1
rCell = i * 2
Next rCell
Next j
Next rngArea
End Sub