Select Dynamic Named Range

[sswcharlie]

Hi

I can set up a code to open a non dynamic named range using :

Range("Contacts1").Select

[sswcharlie]

Oops got away on me.

Start again:

Hi

I can set up a code to open a non dynamic named range using :

Range("Contacts1").Select

It works ok.

However if I change the named range to a dynamic named range, the code does not work.

What is the adjustment to code that I need ?

Thanks
Charlie Harris

[Trebor76]

Hi Charlie,

The syntax is correct and named ranges are meant to be dynamic so that's not the issue.

Is the 'Contracts1' named range correctly being set - you can check this via the 'Refers to:' text box on the 'Define Name' dialog.

Robert

[sswcharlie]

=OFFSET(Contacts1!$G$10,0,0,COUNTA(Contacts1!$G:$G),COUNTA(Contacts1!$1:$1) )

This is the setting for dynamic range. (all in one line)

The range starts at G10 to L15 (currently)
Column G has entries in every row, other columns have some blanks

Thanks

Charlie

[Trebor76]

So do you want to set the 'Contacts1' named range by always starting at G10 and then going to the last row in whatever column from (and including) Col G it resides in?

[sswcharlie]

Hi

Yes. That is correct.

Thanks

Charlie

[Paul_Hossler]

[vba]
=OFFSET(Contacts1!$G$10,0,0,COUNTA(Contacts1!$G:$G)-9,COUNTA(Contacts1!$10$10 )
[/vba]

Hard to tell without sample workbook, but if there's data in G1:G9, my guess is that you'd need to not COUNTA them when you re-size, as well as testing row 10 instead of row 1

Paul

[Trebor76]

Hi Charlie,

Try this:

Sub Macro1()
    '//Declare variables//
    Dim rMyRange As Range
    Dim lLastRow As Long, _
        lLastCol As Long
    Dim sLastCol As String
    
    'If there is no data on the active (current) tab, then...
    If WorksheetFunction.CountA(Cells) = 0 Then
        '...manually set the 'rMyRange' variable to the _
        first (and only) cell in the range, _
        set the 'Contracts1' named range, and _
        quit the routinue.
        Set rMyRange = Range("G10")
        ActiveWorkbook.Names.Add Name:= _
            "Contacts1", RefersTo:=Range(rMyRange.Address)
        Exit Sub
    End If
    
    'Find the last row and column on the active (current) tab by _
    searching through all the rows and columns on the active (current) tab.
    'Source: http://www.ozgrid.com/VBA/ExcelRanges.htm
    lLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lLastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    'If the 'lLastRow' or the 'lLastCol' are less than the _
    starting cell (G10), then...
    If lLastRow < 10 Or lLastCol < 7 Then
        '...manually set the 'rMyRange' variable to the _
        first (and only) cell in the range, _
        set the 'Contracts1' named range, and _
        quit the routinue.
        Set rMyRange = Range("G10")
        ActiveWorkbook.Names.Add Name:= _
            "Contacts1", RefersTo:=Range(rMyRange.Address)
        Exit Sub
    'Else...
    Else
        '...set the 'sLastCol' variable, _
        set the 'rMyRange', and _
        set the 'Contracts1' named range.
        sLastCol = Cells(1, lLastCol).Address(False, False)
        sLastCol = Left(sLastCol, Len(sLastCol) - 1)
        Set rMyRange = Range("G10:" & sLastCol & lLastRow)
        ActiveWorkbook.Names.Add Name:= _
            "Contacts1", RefersTo:=Range(rMyRange.Address)
    End If
    
End Sub

Regards,

Robert

[sswcharlie]

hi Guys

Thanks for the posts.
No luck with first idea.
Second idea from Robert works a treat. A lot of code but it worked first time up. Just tacked on the end code to select the contacts1 and it highlighted what I wanted. Have checked to see if it keeps up with additions of rows and columns and it works fine.

Magic.

Thanks Robert and Paul

Regards

Charlie

[Jan Karel Pieterse]

You could have left the offset function in place and use this one-liner to get there:

application.Goto "Contacts1"