Sabtu, 04 April 2015

Re: [ExcelVBA] Simplify Dynamic Range Code

 

Thank you Bob,


The code that you now gives me is very close to the one that I wrote my-self, and is OK most of the time.

(the line:
 lastrow = .Cells(.Rows.Count, COLUMN_RANGE).End(xlUp).Row
is not needed anymore)

I have written another one that gets only the non zero values, because I need these dynamic ranges for charts that must also be dynamic and stop at the last non zero value.

As for cl, this is the conclusion that I had come to.
It was used because the original script was meant to create on name for each column of a table, and cl was getting the headers to set the names.
So I did not really need it.

I guess I was very tired when I asked for help, because I should have been able to figure it all on my own.
But you helped me a lot in not getting discouraged.

Thanks again,

Regards,

Louise




Le 2015-04-04 à 14:05, 'Bob Phillips' bob.phillips@dsl.pipex.com [ExcelVBA] <ExcelVBA@yahoogroups.com> a écrit :



You are right Louise, that was remiss of me. My solution would create a formula that encapsulated all of your range, but was not dynamic in the sense that it would not accommodate your range changing.
 
This is better, with a COUNTA
 
 
Sub Create_RangeNames()
Const NAME_DYNAMIC_RANGE As String = "MyName"   '<======== change to suit
Const SHEET_NAME As String = "Sheet1"           '<======== 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,COUNTA($" & COLUMN_RANGE & ":$" & COLUMN_RANGE & "),1)"
        End With
    End With
End Sub
 
 
I didn't use cl in my solution, and I don't know why it was declared as object not range, but remember that a range is a type of object, so object whilst not ideal will work okay.
 
Bob
 
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] 
Sent: 04 April 2015 00:13
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Simplify Dynamic Range Code
 
 
Thank you for your prompt reply, Bob.
 
Your script (adapted to my sheet and column) gives this formula:
=OFFSET(CXR!$I$18,0,0,190,1)
where 190 is indeed the last row of the table, but this table will eventually be longer, so it is not exactly a dynamic name.
 
I am only surprised not to find any COUNTA .
 
For example, one of the names that I defined manually could  look like this:
 
=OFFSET(CXR!$I$18,0,0,COUNTA(CXR!$I:$I),1) 
(In fact it is different because I need only the non zero values, but I want to keep it simple here)
 
But it is fine, you've set me on the right path , and I have been able to adapt the script to my need.
 
The other thing that I did not understand in the code was that « cl » object.
Why define it as an object and not as a range?
 
Thank you,
 
Louise
 
 
 
 
Le 2015-04-03 à 07:19, 'Bob Phillips' bob.phillips@dsl.pipex.com [ExcelVBA] <ExcelVBA@yahoogroups.com> a écrit :


 

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


Louise


http://www.experts-exchange.com/Software/Office_Productivity/Q_28518808.html


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: =?iso-8859-1?Q?Louise_Gari=E9py?= <garilou@cgocable.ca>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
----------------------------------
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