Transpose a table's first row into an array

**jimmalk** · 11-23-2012, 01:35 AM

Good evening. A simple "difficult" question. I wish to transpose the header row of a table into an array.

Please Login or Register  to view this content.

The ActiveCell is on a table that's on the worksheet. This code is for the initialization of a form I open. Please help!!

**nilem** · 11-23-2012, 02:01 AM

Hi Jimmalk
just as an example

Please Login or Register  to view this content.

**jimmalk** · 11-23-2012, 02:15 AM

Hi Nilem. Thanks very much for your idea. The worksheet has only one table (~900 columns) and the activecell will always be on that table. Is there a simpler way to get the header row into the array? Another (probably stupid) question. Is "Dim RefTabHeader() as string" needed to make it an array?

**nilem** · 11-23-2012, 02:22 AM

ok

Please Login or Register  to view this content.

**jimmalk** · 11-23-2012, 02:29 AM

Yes, that looks great Nilem. Just 2 questions. Does RefTabHeader need to be declared as "Dim RefTabHeader() As String"? And why a 2-dimentional array if there's only one row to transpose?

**nilem** · 11-23-2012, 05:19 AM

should be so Dim RefTabHeader As Variant (not As String)
For example

Please Login or Register  to view this content.

Upper bound of the first dimension of the arr is equal to 1 (one row), and the upper bound of the second dimension is 4 (4 columns)

**jimmalk** · 11-23-2012, 05:33 AM

I had thought the correct syntax was

Please Login or Register  to view this content.

and I used "String" because the header has strings. I also didnt know it was ok to Dim an array variable without the (). Guess that's why everything I had tried was failing. haha If I understand what you wrote about the array dimensions, one dimension is for the row, and the other for column, so an array used for a table row will always be 2 dimensions. Great. Thanks very much Nilem