empty cells in ranges

[UniDave]

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

Any suggestions, good people

 

[Norman Jones]

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("A120") '<<===== 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.

 

[Norman Jones]

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("A120") '<<===== 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