Jumat, 03 April 2015

RE: [ExcelVBA] Simplify Dynamic Range Code


Sub Create_RangeNames()

Const NAME_DYNAMIC_RANGE As String = "MyName"   '<======== change to suit

Const SHEET_NAME As String = "Sheet4"           '<======== change to suit

Const COLUMN_RANGE As String = "D"              '<======== change to suit

Dim lastrow As Long


    With ActiveWorkbook


        With .Worksheets(SHEET_NAME)


            lastrow = .Cells(.Rows.Count, COLUMN_RANGE).End(xlUp).Row

            .Names.Add Name:="MyName", RefersTo:="=OFFSET('" & SHEET_NAME & "'!$" & COLUMN_RANGE & "$1,0,0," & lastrow & ",1)"

        End With

    End With

End Sub


From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: 03 April 2015 11:10
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Simplify Dynamic Range Code



Hi dear group,

I have always created my dynamic ranged manually.
Now I would need to make it programmatically.

I have found following code, but there are so many syntax lines that I have never used

Am I too lazy? Or still too beginner?

I would like to see something simpler:
Create a dynamic name on only one sheet for only one column, but I am not sure what to take off.
I guess I would need only the code between:
If cl.Value <> "" Then
End If

What is this object named cl?
Is it essential?
Thanks in advance



Sub Create_RangeNames()
'This creates range names based on header row information
Dim wbk As Workbook
Dim sht As Worksheet
Dim rng, rng2 As Range
Dim cl As Object
Dim c As Long
Dim strAddr As Variant
Dim strShName, strHdrName, strCol As String

Set wbk = ActiveWorkbook
For Each sht In wbk.Sheets
c = sht.Cells.SpecialCells(xlLastCell).Column
Set rng = sht.Range("A1", sht.Range("A1").Offset(0, c))
For Each cl In rng
If cl.Value <> "" Then
strShName = Replace(sht.Name, " ", "_", 1)
strHdrName = Replace(cl.Value, " ", "_", 1)
strAddr = Split(cl.Address, "$")
strCol = "$" & strAddr(1) & ":$" & strAddr(1)
Set rng2 = sht.Range(cl, cl.End(xlDown))
ActiveWorkbook.Names.Add Name:=strShName & strHdrName, _
RefersTo:="=OFFSET('" & sht.Name & "'!" & cl.Address & ",0,0,COUNTA('" & _
sht.Name & "'!" & strCol & "),1)"
End If
Next cl
Next sht

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:

More free tutorials and resources available at:




Tidak ada komentar:

Poskan Komentar