GEN, COMM, MAJ etc. are dynamic named ranges on sheet 1, Workbook in scope.
Refers To formula looks like this:

=OFFSET(Sheet1!$T$2,0,0,COUNTA(Sheet1!$T$2:$T$50), 1)

Each range has a header and four entries for testing but when working each will be a different number of rows.

I need them to list each range in column A starting at A2 and a one row space between each range.

The code here lists only the header of each named range in column A and no space between them.

I tried to put a Resize in the posting line but that did not work either, I just used 5 rows by 1 column but it too just posted the headers.

The commented out line did nothing.

I cannot remember the step I am missing to post the entire named range instead of just the header.

Thanks.
Howard



Sub RankArray()

Dim Rank_array(9)
Dim i As Long

Rank_array(0) = Range("GEN")
Rank_array(1) = Range("COMM")
Rank_array(2) = Range("MAJ")
Rank_array(3) = Range("CPT")
Rank_array(4) = Range("LT")
Rank_array(5) = Range("MSGT")
Rank_array(6) = Range("SGT")
Rank_array(7) = Range("CPL")
Rank_array(8) = Range("PVT")

For i = LBound(Rank_array) To UBound(Rank_array)
Range("A" & Rows.Count).End(xlUp)(2) = Rank_array(i)

'Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _
.End(xlUp)(2).Value = Rank_array(i)

Next 'i

End Sub

Hi Howard,

Am Fri, 25 Jul 2014 04:14:29 -0700 (PDT) schrieb L. Howard:

GEN, COMM, MAJ etc. are dynamic named ranges on sheet 1, Workbook in scope.
Refers To formula looks like this:

=OFFSET(Sheet1!$T$2,0,0,COUNTA(Sheet1!$T$2:$T$50), 1)

you have to add your other range names. I only defined 3 of them:

Sub RankArray()

Dim Rank_array As Variant, arrOut As Variant
Dim Rank_Str As String
Dim i As Long
Dim dest As Range

Rank_Str = "GEN, COMM, MAJ"
Rank_array = Split(Rank_Str, ", ")

For i = LBound(Rank_array) To UBound(Rank_array)
Set dest = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
arrOut = Range(Rank_array(i))
dest.Resize(rowsize:=UBound(arrOut)) = arrOut
Next 'i

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Friday, July 25, 2014 4:42:02 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Fri, 25 Jul 2014 04:14:29 -0700 (PDT) schrieb L. Howard:



GEN, COMM, MAJ etc. are dynamic named ranges on sheet 1, Workbook in scope.

Refers To formula looks like this:



=OFFSET(Sheet1!$T$2,0,0,COUNTA(Sheet1!$T$2:$T$50), 1)



you have to add your other range names. I only defined 3 of them:



Sub RankArray()



Dim Rank_array As Variant, arrOut As Variant

Dim Rank_Str As String

Dim i As Long

Dim dest As Range



Rank_Str = "GEN, COMM, MAJ"

Rank_array = Split(Rank_Str, ", ")



For i = LBound(Rank_array) To UBound(Rank_array)

Set dest = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)

arrOut = Range(Rank_array(i))

dest.Resize(rowsize:=UBound(arrOut)) = arrOut

Next 'i



End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thanks Claus, that now looks familiar.

Works good.

Appreciate it.

Regards,
Howard

I don't understand why you're listing the dynamic range names in colA!

Starting off, delete the global scope names unless they're absolutely
necessary!

With the headers, put them in row 1 as fieldnames for the underlying
data and give them each defined names with local scope...

name: sheet1!Gen_Hdr
refersto: =$A$1

...for example of the 1st header. You can create a dynamic range based
on this as follows...

name: sheet1!GenData
refersto: =offset(gen_hdr,1,0,counta($A:$A)-1,1)

...where this will result in a named list of contiguous entries in colA
that does not include the header.

Assuming the next header is colB...

name: sheet1!Comm_Hdr
refersto: =$B$1

...and define a dynamic range based on this as was done for colA...

name: sheet1!CommData
refersto: =offset(comm_hdr,1,0,counta($B:$B)-1,1)

Note that it's now imperative that the data in every row in each
dynamic named col be contiguous to include all entered data. Use a
placeholder for fields that are empty, such as a single space
character.

You can also create a horizontal dynamic named range for the headers as
follows...

name: sheet1!Hdrs
refersto: =offset(Gen_Hdr,0,0,1,counta(1:1))

...so it updates same as the fields when you add/remove items. You can
also create a dynamic range name that includes all the data...

To include headers:
name: sheet1!DataTable
refersto: =offset(hdrs,0,0,counta(GenData)+1)

To exclude headers:
name: sheet1!TableData
refersto: =offset(GenData,0,0,,counta(hdrs))

...where it assumes colA contains 'primary key' values so the scope of
the table is defined as to its record count. If 'GEN' is not a 'primary
key' field then rearrange the order of the headers -OR- use the defined
name of any field that will 'NEVER' be blank.


Now you can 'dump' any range into a 2D array and loop it in a normal
manner to access the data...

Dim vHdrs, vItem, n&
vHdrs = Range("Hdrs")

...which results a 1 row by 9 col array...

For n = 1 To UBound(vHdrs, 2)
Debug.Print vHdrs(1, n) '//print the fieldname
Range(vHdrs(1, n)).Select '//select the range of data -OR-...
For Each vItem In Range(vHdrs(1, n)) '//do something with the data
Debug.Print vItem.Address
Next 'vItem
Next 'n

In the case of not wanting to access individual fieds of data by name,
you can avoid needing to use placeholders as described above (so long
as at least 1 field will never contain blank data) by accessing
individual fields from an array of 'TableData' via Application.Index...

Dim vData, vField, n&, k&
vData = Range("TableData") '//excludes hdrs
'vData = Range("DataTable") '//includes hdrs
For n = 1 To UBound(vData, 2)
'Put each field into an array
vField = Application.Index(vData, 0, n)
For k = 1 To UBound(vField)
Debug.Print vField(k, 1)
Next 'k
Next 'n

If you use code to access individual fields specifically, you can use
an enum so you always get the correct field of data...

Enum Fieldnames
Gen = 1: Comm: Maj: Cpt: Lt: Msgt: Sgt: Cpl: Pvt
End Enum
Public vTableData '//give this global scope so it can be accessed
'from anywhere in code

...where the enum order follows the headers order as listed in the 1st
row of the worksheet. Note that specifying the start number is needed
so the enum matches 1-based array indexing!

Load vTableData at startup and access it like this...

Sub List_CommData()
Dim vData, n&
vData = Application.Index(vTableData, 0, Fieldnames.Comm)
For n = 1 To UBound(vData)
Debug.Print vData(n, 1)
Next 'n
End Sub 'List_CommData

I prefer to use dynamic names for headers ("Hdrs"), all table data
("DataTable"), and just table data ("TableData") and use an enum for
the fieldnames. This reduces the number of defined names needed for
processing via code. For example, the data table might use defined
names in formulas to ref specific col values. These would be defined as
col-absolute/row-relative (local scope), with names that reflect the
headings so user know which fields are being used in the formula. Thus
there's really no need to duplicate that with dynamic range names for
each field (though you could use those in formulas if they are local
scope).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, July 25, 2014 6:08:10 AM UTC-7, GS wrote:


I don't understand why you're listing the dynamic range names in colA!


Hi Garry,

What is going to column A is a copy of the named ranges data after the user makes changes to those ranges.

The actual named ranges are off screen. By using two DV drop downs and some code, data is move FROM one range TO another, then reposted into column A and sheet 2 column A. So far it is working mostly as it needs to. Not to say that improvements could be made, but for me to throw all the weight of your suggestions into this would be pointless as it is far above my pay grade.

Some error checking would most likely make good sense.

Here is a link if you care to take a look, plus there is a link on the sheet to the original thread, I suppose it will work from a linked workbook.


https://www.dropbox.com/s/fe2w5jouk0...rop%20Box.xlsm


Howard

On Friday, July 25, 2014 6:08:10 AM UTC-7, GS wrote:


I don't understand why you're listing the dynamic range names in
colA!


Hi Garry,

What is going to column A is a copy of the named ranges data after
the user makes changes to those ranges.

The actual named ranges are off screen. By using two DV drop downs
and some code, data is move FROM one range TO another, then reposted
into column A and sheet 2 column A. So far it is working mostly as
it needs to. Not to say that improvements could be made, but for me
to throw all the weight of your suggestions into this would be
pointless as it is far above my pay grade.

Some error checking would most likely make good sense.

Here is a link if you care to take a look, plus there is a link on
the sheet to the original thread, I suppose it will work from a
linked workbook.


https://www.dropbox.com/s/fe2w5jouk0...rop%20Box.xlsm


Howard

Ok.., I'll have a look at it over the weekend!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, July 25, 2014 9:00:28 AM UTC-7, GS wrote:


On Friday, July 25, 2014 6:08:10 AM UTC-7, GS wrote:





I don't understand why you're listing the dynamic range names in

colA!





Hi Garry,



What is going to column A is a copy of the named ranges data after

the user makes changes to those ranges.



The actual named ranges are off screen. By using two DV drop downs

and some code, data is move FROM one range TO another, then reposted

into column A and sheet 2 column A. So far it is working mostly as

it needs to. Not to say that improvements could be made, but for me

to throw all the weight of your suggestions into this would be

pointless as it is far above my pay grade.



Some error checking would most likely make good sense.



Here is a link if you care to take a look, plus there is a link on

the sheet to the original thread, I suppose it will work from a

linked workbook.





https://www.dropbox.com/s/fe2w5jouk0...rop%20Box.xlsm





Howard



Ok.., I'll have a look at it over the weekend!



--

Garry


Hi Garry,

FYI, was able twist OP's arm and get a link to the workbook.

Looks like a VLOOKUP and some pretty simple Input Box code will solve the issue.

Time permitting I would be glad to see an example of the code attributes you described.

Knowing it is not a solution to a project, but rather "Here are the techniques and naming procedures"

Maybe stay with the premise of making changes to the dynamic ranges and then posting all the ranges to column A.

Where the good info would be seeing it on the sheet and in the Name Manager etc.

Again, if time permits

Regards,
Howard

Time permitting I would be glad to see an example of the code
attributes you described.

Knowing it is not a solution to a project, but rather "Here are the
techniques and naming procedures"

Maybe stay with the premise of making changes to the dynamic ranges
and then posting all the ranges to column A.

Where the good info would be seeing it on the sheet and in the Name
Manager etc.

After looking at the file you posted a link to on Friday, I realized it
was nothing even remotely like what I read you were trying to do! Where
can I access the link to your OP's file?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion