Evaluating a Range of Empty Cells with VBA

[John Mansfield]

I have 5 columns that start at cell A2 and end at cell E2. The columns
contain both numbers and blank cells. Next, I have two numbers -> 20 and 12.
I am trying to build a macro that will go across the range A1:E1 and flag a
column that has all blank cells between rows 20 and 32 (20 and 20 + 12). I'm
trying to evaluate the range of empty cells but have had no luck. I want to
build something similar to the macro below. How can I build the correct
range offset statement to evaluate the cells between rows 20 and 32 as blank?


Sub FlagColumn()
Dim Rng As Range
For Each Rng In Range("A1:E1")
If IsEmpty(Rng.Offset(rowOffset:=20 to Rng.Offset(rowOffset:=32) = true
then
Do something here . .
End If
Next
End Sub

Thanks.

John Mansfield

 

[Jason Morin]

Try:

Sub FlagColumn()
Dim Rng As Range
Dim ThisWs As Worksheet
Dim NewRng As Range
Const cOffset = 20
Const cHeight = 12

Set ThisWs = ActiveSheet
For Each Rng In Range("A1:E1")
Set NewRng = ThisWs.Cells(Rng.Row + cOffset - 1, _
Rng.Column).Resize(cHeight + 1, 1)
If Application.CountA(NewRng) = 0 Then
MsgBox NewRng.Address(False, False) & " is empty."
End If
Set NewRng = Nothing
Next
End Sub

 

[John Mansfield]

Jason,

Thank you very much - just what I was looking for.

John Mansfield