VBA - Collecting all the heading names into an array

[poduk]

Hi

Please can you help.

I am new to programming and maybe mixing up different languages.
The goal of my code is to collect the names of all the columns currently in my excel sheet.
The excel sheet can change in width (more or less columns) and content (heading names)

Dim i As Integer
i = 0
Range("A1").Activate  ' row 1 contains column headings
Do While Not IsEmpty(ActiveCell)  ' stop if the column heading is empty 
Heading(i) = ActiveCell.Value ' copy the heading name into array positon 0 
ActiveCell.Offset(, 1).Activate  ' move across to the next heading.
    i = i + 1 ' increase array position by 1
Loop ' loop until the end of the headings

I am hoping get an array with all column headings in it
Heading (A1, B1, C1)

Then use heading(0) to call A1 into my code.

Is it possible to do this?
If so, any idea where I am going wrong?

Thanks

Phil

 

[poduk]

This seems to work.
I have set Heading to 60 because it should always be lower than that.

Sub datacollect()
Dim i As Integer
i = 0
Dim heading(60) As Variant
Range("A1").Activate  ' row 1 contains column headings
Do While Not IsEmpty(ActiveCell)
heading(i) = ActiveCell.Value
ActiveCell.Offset(, 1).Activate  ' next cell down
    i = i + 1
Loop
MsgBox heading(0)
End Sub

 

[stumac]

I would avoid setting the number of elements as a constant, it means that if you go on to use the array later you need to check they hold a value. Also avoid using activate where possible:

Sub datacollect()
Dim heading() As String
Dim i As Integer
i = -1
For Each x In Rows(1).Cells
    If x.Value = "" Then Exit For
    i = i + 1
    ReDim Preserve heading(i) As String
    heading(i) = x.Value
Next x
End Sub

 

[poduk]

I would avoid setting the number of elements as a constant, it means that if you go on to use the array later you need to check they hold a value. Also avoid using activate where possible:

Sub datacollect()
Dim heading() As String
Dim i As Integer
i = -1
For Each x In Rows(1).Cells
    If x.Value = "" Then Exit For
    i = i + 1
    ReDim Preserve heading(i) As String
    heading(i) = x.Value
Next x
End Sub

Thank you, I will give it a go.

I have another fairly related question.

I want to use the array's as part of a recordset filter. I
Array 1 (heading(1)), selecting the column in this case "Serial Number"
Array 2 (vRecord(1)), selects the serial number record. for example "123456"

I know filters should look like this but not sure how to do that with array values.
recordset.Filter = "LastName = 'Jones'

I have tried a few different version but they all fail. my last attempt was
recordset.Filter = "heading(1)='" & vRecord(1) & "'"

 

[Rick Rothstein]

The goal of my code is to collect the names of all the columns currently in my excel sheet.
The excel sheet can change in width (more or less columns) and content (heading names)

I am hoping get an array with all column headings in it
Heading (A1, B1, C1)

Assuming your Heading variable is declared as Variant, this single line of code will create a 1-based array from all of the Hearders in Row 1 (assumed to be contiguous with no intermediate blank columns which appears to be what your posted code suggests)...

[table="width: 500"]
[tr]
	[td]Headers = Application.Index(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)[/td]
[/tr]
[/table]

 

[poduk]

You are correct, no blanks.

You code works great and clean the VBA up a lot.

Any idea on the command needed to recordset filters?

 

[Rick Rothstein]

Any idea on the command needed to recordset filters?

Unfortunately, I have almost no experience with databases themselves, so I am not familiar enough with recordsets to be able to advise you. I am sure someone who is familiar with them should come along to answer you question. If that does not happen, you might consider asking your question in a new thread as it is different enough from the purpose of the original thread here to qualify as a new question.

 

[poduk]

Hi

This code works fine for my heading because it has no spaces.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Headers = Application.Index(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)[/TD]
[/TR]
</tbody>[/TABLE]

Is there a code similar to this that will collect the whole row including blanks?

I can use UBound(headers) to get the number of columns (items in the variant) but I am struggling to get a command that works without activeCell (I see the drawbacks of activecell now)

Once I have a code, I plan on looping this until I have collected every row of data in the table.

Thank you for all the help so far.

Phil

 

[Rick Rothstein]

Is there a code similar to this that will collect the whole row including blanks?

Including or excluding?

 

[poduk]

Including blanks

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]heading 1[/TD]
[TD]Heading 2[/TD]
[TD]Heading3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Your command collects all the heading fine because there is no gaps in the headings but the values (row) sometimes have empty cells.
Is there a code that will collect the row regardless of gaps?
Collect row 2 and then a loop to get the following rows.