I've not used arrays much so this is kind of a problem I have at the moment. I need to create an array that has the column numbers of an Excel workbook. The problem is that until I iterate through the columns I don't know which columns I need, nor how many I will have.

So, for example, I need to have an array built with

7, 8, 9, 10, 11

But I am not sure how to add those so that each of those is a separate value in the array. My actual code uses

Do Until .ActiveCell.Value = ""

Need this here assigned to the array: .ActiveCell.Column
.ActiveCell.Offset(0,1).Select
Loop



can anyone help?

One way to do this is to use Preserve Redim:

Private Sub Tryit()

Dim foo() As Integer

For i = 0 to 100
Redim Preserve foo(i)
foo(i)=1
Next

End Sub

But Redim Preserve is quite expensive. In this case I use Collection which is much easier to manage with an array of unknown dimension:

Dim col As New Collection

For i = 0 to 100
col.Add("1", i)
Next i

End Sub

Note that collection also has "keys" argument, which you can supply to provide a quick reference to individual elements of the collection. They also can store a variety of datatypes, so you could store strings and integers in same collection and pull them out, providing you have the references to the elements.

HTH.

Hi Bob

I had an issue similar to yours whereby I was reading data from a spreadsheet that had blank rows, this meant I could not use the ActiveCell = "" theory. So I used the following to get get the last row and column of a spreadsheet

Dim iRow As Long
Dim iCol As Long

iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Colu mn

With ActiveSheet.UsedRange
iCol = .Cells(1, 1).Column + .Columns.Count - 1
iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With

This tells me the bottom right cell reference of the spreadsheet.

So if you declare your array

Dim Array(irow,icol)

This will ensure that you use the least number of rows and columns.

David

I'm guessing this is related to the question you posted in the excel forum. If I need to build an array for this I would usually do it like:


Dim SubTColumns()

LastCol = cells(1,255).end(xltoleft).column

redim preserve SubTColumns(0 to LastCol - 7)

for i = 7 to LastCol
SubTColumns(i - 7) = i
next i


You can then pass the SubTColumn array to the subtotal method