Getting the values for the cells if there is 1 or more values assigned

user_10057790 0
Viewed 22

How do I fix this if I have cells having values for 2 or more for SHEET1 in columns A & B with this code: (I don't have any problems)

Dim LineNumbers() As Variant
LineNumbers = Range("A1", ActiveSheet.Cells(Rows.Count, "A").End(xlUp))
Dim Lengths() As Variant
Lengths = Range("B1", ActiveSheet.Cells(Rows.Count, "B").End(xlUp))

But if there is only 1 value for the row on columns A & B this raises an error. I read here that if I use usedrange, CountA and xldown is not recommended to get the last used row as it causes some errors for the results, then the code above is much efficient. Unfortunately for me when I have only 1 value in my LineNumbers and Lengths this raises an error.

variant vba arrays
2 Answers

Declaring Dim LineNumbers() As Variant your code tells to VBA that it must keep an array. An array of anything (strings, numbers, objects)...

Then, LineNumbers = Range("A1", ActiveSheet.Cells(Rows.Count, "A").End(xlUp)) is similar with LineNumbers = Range("A1","A1"), or LineNumbers = Range("A1"). Such a range cannot return an array (from a single cell).

But, if you change the declaration:

Dim LineNumbers As Variant

It will not return an array, any more, but not being mandatory to be an array, it will take the cell value.

Please, see the next adapted code:

Dim LineNumbers As Variant
  LineNumbers = Range("A1", ActiveSheet.cells(rows.count, "A").End(xlUp))
  If IsArray(LineNumbers) Then 'for the case of only one cell
        Debug.Print LineNumbers
  Else
        Dim El As Variant
        For Each El In LineNumbers
            Debug.Print El
        Next El
  End If
user_22333080

Writing From a Range to an Array

Dim LineNumbers As Variant
Dim Lengths As Variant

With ActiveSheet
    With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
        If .Rows.Count = 1 Then ' one cell
            ReDim LineNumbers(1 To 1, 1 To 1): LineNumbers(1, 1) = .Value
        Else ' multiple cells
            LineNumbers = .Value
        End If
    End With
    With .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
        If .Rows.Count = 1 Then ' one cell
            ReDim Lengths(1 To 1, 1 To 1): Lengths(1, 1) = .Value
        Else ' multiple cells
            Lengths = .Value
        End If
    End With
End With
user_98140690
Related