Evaluating a Range of Empty Cells with VBA

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

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

---
HTH
Jason
Atlanta, GA

>-----Original Message-----
>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,

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

John Mansfield

"Jason Morin" wrote:

> 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
>
> ---
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >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
> >.
> >
>