Find a Range and then name it

[gheyman]

I have a sheet where in Column C the user will enter a list of Part numbers. I need to define that range.

My hurdle is I can never be sure what row that list of Part Numbers begins with (and I cannot make it a table). The user can and will insert rows above where the list begins.

Is there a way to determine which row in column C the title "PartNumber" is and then define and name the range from there down to the last row?

I would like to name the Range: PartNumberRange

Thanks!
Greg

 

[mumps]

Try:

Sub NameRange()
    Dim fnd As Range
    Set fnd = Range("C:C").Find("PartNumber", LookIn:=xlValues, lookat:=xlWhole)
    Range("C" & fnd.Row + 1, Range("C" & Rows.Count).End(xlUp)).Name = "PartNumberRange"
End Sub

 

[gheyman]

Perfect. Thank you!

 

[mohadin]

Hi
May be

Sub test()
    x = Cells.Find("PartNumber", , , 1).Row
    Z = Range(Cells(x, 3), Cells(x, 3).End(xlDown)).Rows.Count
    ActiveWorkbook.Names.Add Name:="PartNumber", RefersToR1C1:="=Sheet1!R" & x & "C3:R" & x + Z & "C3"
    Sheets("sheet1").Range("PartNumber").Select
End Sub

 

[mumps]

You are very welcome.