Advice: Dynamic named range Discontinuous, Non-VBA

[brettdj]

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

[Zack Barresse]

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.

[xXLdev]

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.

[brettdj]

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

[xXLdev]

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?

[brettdj]

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

[xXLdev]

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.

[Zack Barresse]

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.

[byundt]

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.

[brettdj]

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