I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Code
Dim sNm As String, sRT As String
If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->
sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"
ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT
Display More
My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.
Thanks in advance for the help!