Re: [ExcelVBA] Dynamic Named Range VBA (naming of range) not working

 

-----Original Message-----
From: 'Bob Phillips' bob.phillips@dsl.pipex.com [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Sun, Jun 15, 2014 9:07 pm
Subject: RE: [ExcelVBA] Dynamic Named Range VBA (naming of range) not working

How about this?

 

Sub AddDynamicRangeHorizontal()

    On Error Resume Next

    Dim sRangeName As String

    Dim n As Name

 

    If ActiveWorkbook Is Nothing Then Exit Sub

 

    sRangeName = InputBox("Enter a range name, then push OK. ", _

    "Add Horizontal Dynamic Range")

 

    If sRangeName = "" Then Exit Sub

 

    sRangeName = Replace(sRangeName, " ", "_")

 

    With ActiveSheet

   

        ActiveWorkbook.Names.Add Name:=sRangeName, _

                                 RefersTo:="=OFFSET('" & .Name & "'!" & _

                                 ActiveCell.Address & ",,," & _

                                    "COUNTA('" & .Name & "'!" & Columns(ActiveCell.Column).Address & ")," & _

                                    "COUNTA('" & .Name & "'!" & Rows(ActiveCell.Row).Address & "))"

    End With

 

    For Each n In ActiveWorkbook.Names

        If n.Name = sRangeName Then Exit Sub

    Next n

 

    MsgBox Err.Description, , "Invalid Name"

 

    On Error GoTo 0

End Sub

 

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: 15 June 2014 07:58
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Dynamic Named Range VBA (naming of range) not working

 

 

Hi

 

Came across this vba some time ago. Just got round to trying it.

 

It is designed to give a name to range and then set up dynamic range in the   RefersTo:  box

 

Place cursor position in top left position It runs and allows name to be done.  But it selects all columns to the right and does not select any rows apart from the first row.  Places code in the refers to box ok.

 

Say if a range is selected I thought it would show all rows selected, but only showing the first row.  With columns it just needs to select those columns selected.

This row the prblem I think - it selects the whole row. Is there such a thing as an  ActiveRange  ?

 

 & "!" & Rows(ActiveCell.Row).Address & "))"

 

Your advice would be appreciated.

 

Thankyou

Charles Harris

 

 

Sub AddDynamicRangeHorizontal()

    On Error Resume Next

    Dim sRangeName As String

    Dim n As Name

 

    If ActiveWorkbook Is Nothing Then Exit Sub

 

    sRangeName = InputBox("Enter a range name, then push OK. ", _

    "Add Horizontal Dynamic Range")

 

    If sRangeName = "" Then Exit Sub

 

    sRangeName = Replace(sRangeName, " ", "_")

 

    ActiveWorkbook.Names.Add Name:=sRangeName, _

    RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _

    & ActiveCell.Address & ",,,,COUNTA(" & ActiveSheet.Name _

    & "!" & Rows(ActiveCell.Row).Address & "))"

 

    For Each n In ActiveWorkbook.Names

        If n.Name = sRangeName Then Exit Sub

    Next n

 

    MsgBox Err.Description, , "Invalid Name"

 

    On Error GoTo 0

End Sub

 

 

__._,_.___

---

Posted by: Green <1z@compuserve.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___