Filling an array with 1 row of data

[jay_hl]

Hello

I have an array which has a range of data (e.g B2:B10). Because this column can move, and the number of rows are dynamic, I have coded this to have a StartRow, LastRow, and a search of a column number.

The line of code works well, when there are multiple rows (e.g StartRown< Last Row) ie a range from row 2 to 10), but when there is only 1 row of data (e.g StartRow=LastRow ie just row 2) then this line fails.

In this example:
StartRow = 2
LastRow = 2
Column1 = 8

So I would like to full the Field1 array with H2:H2

Can anyone help where I'm going wrong?

Regards

Jay

 

[Alex Blakenburg]

When you only assign a single cell to a variant it doesn't create an array,
There are a few different ways of handling this. I probably use a less conventional method put it means that I don't have to change any other parts of the code:

Sub HandleSingleCellRange()

Dim rngField1 As Range, arrField1 As Variant
Dim StartRow As Long, LastRow As Long, Column1 As Long
StartRow = 2
LastRow = 2
Column1 = 8


Set rngField1 = Range(Cells(StartRow, Column1), Cells(LastRow, Column1))
arrField1 = rngField1

If IsArray(arrField1) = False Then
    ReDim arrField1(1 To 1, 1 To 1)
    arrField1(1, 1) = rngField1.Value
End If

End Sub

 

[jay_hl]

Yeah I started with a similar approach (using an If but comparing LastRow>StartRow) but I wondered if there was a easier/smarter way with how the Array gets set and filled with 1 row.

 

[Alex Blakenburg]

It only happens if you have a single cell. If you had 2 columns instead of 1 that wouldn't cause the issue.
A common approach it to handle the single cell case separately, I just prefer to forcibly create an array so I can use the loop I am already using for the array without handling an exception.

 

[kvsrinivasamurthy]

Does this helps .

Sub macro()
Stro = 2
Lro = 4
clm = 8

If Stro = Lro Then
ReDim arrfield1(1 To 1, 1 To 1)
arrfield1(1, 1) = Cells(Stro, clm).Value
Else
ReDim arrfield1(1 To Lro - Stro + 1, 1 To 1)
arrfield1 = Range(Cells(Stro, clm), Cells(Lro, clm))
End If

End Sub