Dynamic Named Ranges

[Risk]

Hi,

I'm trying to set dynamic named ranges using vba instead of the offset option I've seen on some forums. Here is what I have so far, but I'm running into a problem with the last two named ranges that reference the LastColumn and DataRng. I'm definitely a beginning in vba so go easy on my code, it took my 4 hours to write with trial and error, but please feel free to point out better methods and what is wrong with the code.

Thanks in advance,
Risk

Sub ConstructionNamedRange()

Dim LastRow As Long
Dim LastColumn As Integer
Dim DataRng As Range

Worksheets("Construction").Activate
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Column

Set DataRng = Range(Cells("1", "A"), Cells(LastRow, LastColumn))

    '''Names Site
    ThisWorkbook.Names.Add Name:="c_Site", _
    RefersTo:=Worksheets("Construction").Range("A1:A" & LastRow)
    
    '''Names Type
    ThisWorkbook.Names.Add Name:="c_Type", _
    RefersTo:=Worksheets("Construction").Range("B1:B" & LastRow)
    
    '''Names Category
    ThisWorkbook.Names.Add Name:="c_Category", _
    RefersTo:=Worksheets("Construction").Range("C1:C" & LastRow)
    
    '''Names New Construction
    ThisWorkbook.Names.Add Name:="c_Construction", _
    RefersTo:=Worksheets("Construction").Range("D1:D" & LastRow)
     
    '''Names Forecast
    ThisWorkbook.Names.Add Name:="c_Forecast", _
    RefersTo:=Worksheets("Construction").Range("E1:E" & LastRow)
    
    '''*****************************************************'''
        '''**** Problem starts here****'''
    '''*****************************************************'''
    
    '''Names Header
    ThisWorkbook.Names.Add Name:="c_header",
    RefersTo:=Worksheets("Construction").Range("A1:" & LastColumn & "1")
    
    '''Names Data Range
    ThisWorkbook.Names.Add Name:="c_Data", _
    RefersTo:=Worksheets("Construction").Range(DataRng)

End Sub

 

[mikerickson]

There is a non volatle native excel solution.
Name: c_Site
RefersTo: INDEX(Construction!$A:$A, 1, 1):INDEX(Construction!$A:$A, MATCH(REPT("z",255),Construction!$A:A), 1)

(If you anticipate the last entry in column A to be numeric, change the REPT("z",255) to 9E+314.
)

Name: c_Type
RefersTo: INDEX(Construction!$B:$B, 1, 1):INDEX(Construction!$B:$B, ROWS(c_Site), 1)

etc.

As to your VB, this should take care of c_Header

ThisWorkbook.Worksheets("Construction").Range("A1").Resize(1, LastColumn).Name = "c_header"

Its not clear to me what the issue with DataRange is.

 

[Risk]

Mikerickson,

Thanks for your help. The fix works

ThisWorkbook.Worksheets("Construction").Range("A1").Resize(1, LastColumn).Name = "c_header"

using your string, I was able to correct the data range

ThisWorkbook.Worksheets("Construction").Range("A1").Resize(LastRow, LastColumn).Name = "c_Data"

I'm still playing around with

There is a non volatile native excel solution.
Name: c_Site
RefersTo: INDEX(Construction!$A:$A, 1, 1):INDEX(Construction!$A:$A, MATCH(REPT("z",255),Construction!$A:A), 1)

(If you anticipate the last entry in column A to be numeric, change the REPT("z",255) to 9E+314.
)

Name: c_Type
RefersTo: INDEX(Construction!$B:$B, 1, 1):INDEX(Construction!$B:$B, ROWS(c_Site), 1)

It seems to work, though hides it in the name box as if Visible:=False. Then again maybe I'm doing something wrong. Thank you for your help, I appreciate you showing both the native and VBA solutions.

-Risk