Note1:

for multiarea ranges the only reliable way to define a name
with VBA is setting the name property of the range object

ofcourse you could just use a filter.. but this is an alternative
and works well PROVIDED you will not find more than 500 or so
non-consecutive records.. the union method is very slow on large
multiareas.

Sub foo()

Dim rngCel As Range
Dim rngTbl As Range
Dim rngSel As Range

With ActiveSheet
Set rngTbl = Intersect(.Range("C:C"), .UsedRange)
End With
'start the union with a cell outside the table
Set rngSel = rngTbl.Worksheet.Range("IV1")
'loop the cells
For Each rngCel In rngTbl
If rngCel.Value = 1 Then Set rngSel = Union(rngSel, rngCel)
Next

If rngSel.Count = 1 Then
MsgBox "No records found"
Else
'Remove the "IV1"
Set rngSel = Intersect(rngSel, rngTbl)

'Expand to entirerows
Set rngSel = rngSel.EntireRow

'Assign the name at worksheetlevel
rngSel.Name = "'" & rngSel.Worksheet.Name & "'!myrecords"
End If

End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


jhahes wrote :


How do I name a range with an if statement

Say on a worksheet change on sheet4

If I add a record to the database the new record will go into a
certain range.



Here is what i am trying to do

Search thru say Column C

If the search finds a 1 then it would select it or activate it

All the selected fields would be named a range (Range_1)


In my case all records are sorted so all the ones would be
consecutive, but could you do this same scenario with records out of
order


I would appreciate any input or help

Thank you for your time

Josh