Range calculations with cell refrences

[Spikenaylor]

my code is this

GetCellsFilled = Application.CountA(Range("C2:C7"))

This works

but if I use cell references ie.

GetCellsFilled = Application.CountA(Range(cref1:cref2))

or

GetCellsFilled = Application.CountA(Range("cref1:cref2"))

I get Method 'Range' of object'_Global' failed

Any pointers,

thanks
Mike

 

[Special-K99]

"C2:C7"

That's text
so surely

cref1:cref2

this needs to be text too
Try

GetCellsFilled = Application.CountA(Range(""& cref1 & ":" & cref2 & ""))

Am not sure I got the correct number of double quotes, could be 2 could be 3, am No VBA expert

 

[VoG]

Try

GetCellsFilled = Application.CountA(Range(cref1 & ":" & cref2))

 

[Spikenaylor]

Many thanks for the quick replies,

as ever a simple solution that had me foxed.

Cheers

Mike