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

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

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

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

Thanks Tom


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=570348