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: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar