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]

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


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"