PDA

View Full Version : Select Dynamic Named Range



sswcharlie
05-17-2011, 06:50 PM
Hi

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

Range("Contacts1").Select

sswcharlie
05-17-2011, 06:57 PM
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
05-17-2011, 07:03 PM
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
05-17-2011, 09:24 PM
=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
05-17-2011, 09:41 PM
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
05-17-2011, 09:55 PM
Hi

Yes. That is correct.

Thanks

Charlie

Paul_Hossler
05-18-2011, 05:38 AM
=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
05-18-2011, 05:10 PM
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
05-18-2011, 09:39 PM
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
05-18-2011, 11:20 PM
You could have left the offset function in place and use this one-liner to get there:

application.Goto "Contacts1"