Define Range Name with variable row value

[wayliff]

I'm trying to define a name range using variable rows but somehow the range name does not get calculated.
I currently have this code which works with fixed ranges.

What to do to use variable rows?

'Deploy Formula on both sheets to find data differences
Dim lastrowquery As Integer
Dim lastrowreport As Integer
lastrowquery = Sheets("sheet1").Range("C1").End(xlDown).Row
lastrowreport = Sheets("sheet2").Range("C6").End(xlDown).Row
'Assign Range Names before Formulas
Range("C1:C" & lastrowquery).Select
ActiveWorkbook.Names.Add Name:="rangename", RefersToR1C1:= _
"='sheet1'!R1C3:R348C3"
Sheets("sheet2").Select
Range("J6").Select
ActiveCell.FormulaR1C1 = "=ISNA(MATCH(RC[-7],rangename,FALSE))"
Selection.AutoFill Destination:=Range("J6:J" & lastrowreport), Type:=xlFillDefault

Suggestions greatly appreciated...

Thanks

[Toppers]

Hi,
Try:

ActiveWorkbook.Names.Add Name:="rangename", RefersToR1C1:= _
"='sheet1'!R1C3:R" & lastrowquery & "C3"


HTH

"wayliff" wrote:

>
> I'm trying to define a name range using variable rows but somehow the
> range name does not get calculated.
> I currently have this code which works with fixed ranges.
>
> What to do to use variable rows?
>
> 'Deploy Formula on both sheets to find data differences
> Dim lastrowquery As Integer
> Dim lastrowreport As Integer
> lastrowquery = Sheets("sheet1").Range("C1").End(xlDown).Row
> lastrowreport = Sheets("sheet2").Range("C6").End(xlDown).Row
> 'Assign Range Names before Formulas
> Range("C1:C" & lastrowquery).Select
> ActiveWorkbook.Names.Add Name:="rangename", RefersToR1C1:= _
> "='sheet1'!R1C3:R348C3"
> Sheets("sheet2").Select
> Range("J6").Select
> ActiveCell.FormulaR1C1 = "=ISNA(MATCH(RC[-7],rangename,FALSE))"
> Selection.AutoFill Destination:=Range("J6:J" & lastrowreport),
> Type:=xlFillDefault
>
> Suggestions greatly appreciated...
>
> Thanks
>
>
> --
> wayliff
> ------------------------------------------------------------------------
> wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
> View this thread: http://www.excelforum.com/showthread...hreadid=502040
>
>

[wayliff]

I feel pretty stupid since I tried that already but was missing one " quotation.

Thanks for your help...made my hour.