If the range will Always be contiguous

Dim rng as range, rng1 as Range
Range("D2:S4000").Name = "Table1"
if Activesheet.AutoFilterMode then
set rng = Activesheet.AutoFilterRange
rng.offset(1,0).Resize(rng.rows.count-1)
On Error Resume Next
set rng1 = rng.SpecialCells(xlVisible)
On Error goto 0
if rng1 is nothing then
msgbox "No visible rows"
exit sub
elseif rng1.areas.count 1 then
msgbox "Filtered area is not contiguous"
exit sub
end if
rng1.Name = "Table1"



=IF(S4="","",VLOOKUP(S4,Table1,16,FALSE))

--
Regards,
Tom Ogilvy


"MH UK" wrote in message
...
I have a vlookup formula, but want to programatically change the range the
look up looks at.
The data set is filtered by vba by user selection from a drop down list
If I want to change
=IF(S4="","",VLOOKUP(S4,MFData!$D$2:$S$4000,16,FAL SE))
to the same range as is filtered.
In one scenario the data range is filtered to rows 2008 to 2243 (rather
than
2 to 4000), how can I change the formula to
=IF(S4="","",VLOOKUP(S4,MFData!$D$2008:$S$2243,16, FALSE))
Presumably by defining a named range each time the filter is applied.

Thanks in anticipation