Arrays

llewellynj · June 17th, 2003, 04:08 PM

Please Help!

I have some data in a colum that can vary in the amount of rows. I would like to assign this data to an array. I would then like to populate some columns in my worksheet the data in the array.

Many thanks
Llewellyn Jones

Tek4VB · July 3rd, 2003, 12:03 PM

use a loop to iterate the cells, placing each cells' data into a variable of an array

Use dynamic arrays if the amount of cells can vary.

hope that helps
Tek

Ben · July 3rd, 2003, 12:07 PM

Why not create a named range as it will automatically vary in size.

Ben

alex_read · July 8th, 2003, 05:50 AM

Quick sample....

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngUsedColACells As Range
    Dim strAryColACellVals() As String
    Dim intLoopCounter As Integer

    Set rngUsedColACells = Range("A1:A" & Worksheets(1).UsedRange.Rows.Count)
    intLoopCounter = 0

    For Each cell In rngUsedColACells
        ReDim Preserve strAryColACellVals(intLoopCounter)
        strAryColACellVals(intLoopCounter) = cell.Value

        intLoopCounter = intLoopCounter + 1
    Next cell
End Sub