Counting blank cells in one column

[Casey]

Hi,
I have two entire rows which I have made Named Ranges.
1. "CopyDestinationRow2" (actually Row 9 and never changes)
2. "BelowEntryBottomRow2" (varies from Row 10 to ???) Rows are added and subtracted all the time)

What I need to do is count the number of blank cells in column B only from "CopyDestinationRow2" to "BelowEntryBottomRow2".

I have been doing this with a third named range "RFPEntryArea" which is column B from "CopyDestinationRow2" to "BelowEntryBottomRow2" and the following code.
Cnt2 = Sheets("Request for Proposal Form").Range("RFPEntryArea") _
.SpecialCells(xlCellTypeBlanks).Count
The problem is even on a protected sheet if someone uses Cut and paste on column B it changes "RFPEntryArea" and the remaining code goes erratic.

[Tom Ogilvy]

Dim rng1 as Range, rng2 as Range, rng3 as Range
set rng1 = Range("CopyDestinationRow2")
Set rng2 = Range("BelowEntryBottomRow2")
set rng1 = Intersect(rng1,rng1.parent.columns(2))
set rng2 = Intersect(rng2,rng2.parent.columns(2))
set rng3 = rng1.parent.Range(rng1,rng2)
cnt2 = Application.countblank(rng3)

' or
' cnt2 = rng3.specialcells(xlblanks).count

--
Regards,
Tom Ogilvy


"Casey" wrote:

>
> Hi,
> I have two entire rows which I have made Named Ranges.
> 1. "CopyDestinationRow2" (actually Row 9 and never changes)
> 2. "BelowEntryBottomRow2" (varies from Row 10 to ???) Rows are added
> and subtracted all the time)
>
> What I need to do is count the number of blank cells in column B only
> from "CopyDestinationRow2" to "BelowEntryBottomRow2".
>
> I have been doing this with a third named range "RFPEntryArea" which is
> column B from "CopyDestinationRow2" to "BelowEntryBottomRow2" and the
> following code.
> Cnt2 = Sheets("Request for Proposal Form").Range("RFPEntryArea") _
> .SpecialCells(xlCellTypeBlanks).Count
> The problem is even on a protected sheet if someone uses Cut and paste
> on column B it changes "RFPEntryArea" and the remaining code goes
> erratic.
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=570348
>
>

[Casey]

Tom,
It works great. Thank you for the help. I see the use of the intersect function. I'm pretty good with excel worksheet functions, mostly because of the Insert Function dialog box and the help available there. Is there a similar function in the VBE or a list of available VBA functions?

Casey

[Tom Ogilvy]

The object browser in the VBE or the Help in the VBE is what most use.

--
Regards,
Tom Ogilvy


"Casey" wrote:

>
> Tom,
> It works great. Thank you for the help. I see the use of the intersect
> function. I'm pretty good with excel worksheet functions, mostly
> because of the Insert Function dialog box and the help available there.
> Is there a similar function in the VBE or a list of available VBA
> functions?
>
> Casey
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=570348
>
>

[Casey]

Thanks Tom