Consulting

Results 1 to 10 of 10

Thread: Advice: Dynamic named range Discontinuous, Non-VBA

  1. #1
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

    Question Advice: Dynamic named range Discontinuous, Non-VBA

    I saw a query on another board today where a user wanted to graph every nth point in a range directly from that range (ie without creating a sub range of nth points).

    It got me wondering if it is possible to generate a discontinuous dynamic named range that would select say every 10th point. I don't think that it is doable - any ideas?

    Cheers

    Dave

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Nope, I don't think so. You can name it, and it'll go that far. But as far as manipulating that data in formulas or graphs I think your SOL.

  3. #3
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    Here is the code to select every other row in a selection. The code could be made more generic and prompt the user for how many rows to skip between selection.

    Sub SelectEverySecond()
        Dim rwStart As Integer, rwEnd As Integer, rwCur As Integer
        Dim colStart As Integer, colEnd As Integer
        Dim r1 As Range, r2 As Range, rFinal As Range
    rwStart = Selection.Row
        rwEnd = rwStart + Selection.Rows.Count - 1
        colStart = Selection.Column
        colEnd = colStart + Selection.Columns.Count - 1
    rwCur = rwStart
        Set rFinal = Range(Cells(rwCur, colStart), Cells(rwCur, colEnd))
        rwCur = rwCur + 2
    While rwCur <= rwEnd
        Set r2 = Range(Cells(rwCur, colStart), Cells(rwCur, colEnd))
        Set rFinal = Union(rFinal, r2)
        rwCur = rwCur + 2
        Wend
        rFinal.Select
    End Sub
    Then you can run this code to create the chart of the new selection:

    Sub ChartIt()
        Dim strName As String
    strName = ActiveSheet.Name
    Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.Location Where:=xlLocationAsObject, Name:=strName
    End Sub
    To test the code, put chart labels in column A and chart values in column B. Select all the data like you would when creating a chart. Run the first macro then the second. You should now have a chart with every other point charted.

    Enjoy.

  4. #4
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Thanks for the reply and code

    My initial post was a speculative query wondering if it was possible to generate a dynamic named range with multiple sections, ie non VBA. I didn't think it was possible but I thought I'd test the water just in case.

    Cheers

    Dave

  5. #5
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    Dave,

    I wasn't sure what exactly you wanted. So I coded it up for fun.

    To make sure I understand, you wanted somthing like Edit/Go to/Special that allowed one to automatically select every N rows. Or am I still completely clueless?

  6. #6
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    I didn't post enough info - my apologies.

    With a dynamic range name below I can refer to a range starting in A1 extending down by the number of non-empty cells in Column A.

    ie
    Insert - Name - Define
    Name: MyRange
    refers to =OFFSET($A$1,0,0,COUNTA($A:$A),1)

    would refer to A1:A10 if there were 10 non-empty cells in Column A

    I was wondering if there was a way to create a dynamic range name that refered to say, every tenth non-empty A cell (A1,A11,A21 etc)

    Speculative, but interesting

    Cheers

    Dave

  7. #7
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    Now I get it! Sometimes I am a little slow.

    I don't think you will be able to create a dynamic range. Interesting idea. If you could, I thiink it would be about as complicated as the VBA code.

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    There is a workaround though. If, as in your last example Dave, you have a list of information in column A in which you want to extract every nth row of information to a populated list or dynamically named range. This can be done with the addition of a second column. At least that's the only way I know how to do it.

    We'll say your looking at column A, full of information. You only want every 10th row of information starting with row 1 (as in your last post: 1, 11, 21, 31, etc.). In column B you could put something like this...

    =INDEX($A:$A,(CELL("row",$A1)-CELL("row",$A$1))*$C$1+1)
    ..and copy down. Then use a dynamic range, as your formula above looks (or I prefer an INDEX/MATCH combination instead of the OFFSET), or:

    =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$B:$B))
    This is of course if you have text in column A. If your formula goes past the nth row it will show 0. Which you could always use custom formatting on, something like this:

    [=0]"";@;General
    Will give you the same results w/o using OFFSET. Change range/sheet name to suit. But it works.

    Maybe you could use this, hope it helps.

    Example is uploaded.

    EDIT: BTW, cell D1 in the sample file has the DV drop-down list from column B.
    Last edited by Zack Barresse; 06-12-2004 at 01:47 PM.

  9. #9
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    Here is a dynamic named range that returns every third point:
    =INDEX(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1),3*ROW(INDIRECT("1:" & INT(COUNTA(Sheet1!$A:$A)/3))),1)
    While I could paste this formula to a worksheet using an array formula, I couldn't get it to work with a chart series.
    Last edited by byundt; 06-14-2004 at 06:17 PM. Reason: grammar

  10. #10
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Nice workaround firefytr, thanks for your example

    Hi Brad,

    Its good to see you here. Cute solution, it is a shame that the chart series doesn't like it.

    It worked well as an array formula. One query, I tried it as a defined name and then tried indexing the defined name to return the nth element - no joy.

    Cheers

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •