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

 

[Guest]

Hi,
Try:

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


HTH

 

[wayliff]

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

Thanks for your help...made my hour.