How to create an array from data on worksheet

[jazztech]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a large amount of data on a worksheet (in a single column) and I would like to create an array from it. My code asks the user to input the first row and the letter of the column where the data is and the code finds the last row so I have three variables to describe where the data is coming from.
<o></o>
<o></o>
How could I create an array from this to be able to refer to it in the code later on?
<o></o>
<o></o>
Any input is greatly appreciated.<o></o>
<o></o>
Thanks in advance!<o></o>
<o></o>
Jazztech

My code so far:

Sub histogram()

'USER INPUTS

Dim FirstRow As Integer
FirstRow = InputBox(prompt:="FirstRow", Default:=18)

Dim FLCol As String
FLCol = InputBox(prompt:="FL Vel Column", Default:="b")

Dim acs As Worksheet
Set acs = ActiveWorkbook.ActiveSheet

Dim LastRow As Long
LastRow = acs.Cells(FirstRow, FLCol).End(xlDown).Row

 

[Andrew Poulsom]

Try:

    Dim MyArray As Variant
    With acs
        MyArray = .Range(.Cells(FirstRow, FLCol), .Cells(LastRow, FLCol)).Value
    End With

 

[jazztech]

Great, many thanks!

 

[jazztech]

Andrew,

Would you mind to polish my dull mind a little bit further, please?
Maybe I am confused with array vs range having data in it.

I used your suggestion to create the data array to be able to use it in the frequency function.

Dim DataArray As Variant
With acs
DataArray = .Range(.Cells(FirstRow, FLCol), .Cells(LastRow, FLCol)).Value
End With

I created the bin array with the following code (the variables are user input because the size and resolution of the bin array can change)

Dim BinArray() As Integer
ReDim BinArray(1 To NOE)

Dim intI As Integer

Dim BinElement As Integer
BinElement = MaxBin

For intI = 1 To NOE
BinArray(intI) = BinElement

BinElement = BinElement - BinWidth

Next

And used the frequency function as

Dim FreqArray() As Variant
ReDim FreqArray(1 To NOE)

FreqArray = Application.WorksheetFunction.frequency(DataArray, BinArray)

Why I am not able to declare any other type than VARIANT both for the DataArray and the FreqArray?

The DataArray consist only numbers with one digit accuracy e.g.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num>20.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>19.9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>19.7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>19.3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>18.9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>18.3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>17.6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>16.9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e2e2e2; BORDER-TOP: #e2e2e2; BORDER-LEFT: #e2e2e2; BORDER-BOTTOM: #e2e2e2; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>16</TD></TR></TBODY></TABLE>

and the frequency function by nature cannot produce anything else than integers. I have problems to display the output of the frequency function but I guess I do something wrong with the array generations.

Many thanks in advance,