Handling an array with one element in a for each loop

Question

Patients Table in Excel

            A
 1          |Patient Name  |
 2          |Jane Doe      |

When this table has just one patient listed, the following routine fails:

Sub RunMerge()

Dim ptsArray As Variant
Dim strPtName As Variant
Dim lRowCount As Long

lRowCount = Worksheets("Patients").UsedRange.Rows.Count

ptsArray = ThisWorkbook.Worksheets("Patients").Range("A2:A" & lRowCount).value

    For Each strPtName In ptsArray
            . . .
    Next
End Sub

Within the VBA editor I see strPtName is showing empty but ptsArray shows that it contains Jane Doe. And the program stalls. But if I add a second patient or more all works as it should. What am I missing in order to run the routine in the event that only one patient is left.

http://www.cpearson.com/excel/ArraysAndRanges.aspx... a single cell's `.Value` is not an array, and thus is a special case that you have to handle. — BigBen, Jun 09 '20 at 16:46
Btw, arrays ask for a `For Lbound() to UBound()` loop, not a `For Each`. — BigBen, Jun 09 '20 at 16:47
Thanks BigBen. That is my question, how do I handle this case? — Craig Tucker, Jun 09 '20 at 16:52
Test whether the range has only one cell. If so do your logic on that one value. If not then loop the array. — Scott Craner, Jun 09 '20 at 16:56
Also, `UsedRange` is unreliable... see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for a better way to find the last row. — BigBen, Jun 09 '20 at 16:58
Thanks BigBen. My code is updated with your suggestions. I was having a hard time quickly adapting the principals from cpearson.com . Vbasic2008 made it plain. — Craig Tucker, Jun 09 '20 at 21:06

VBasic2008 · Accepted Answer · 2020-06-09T18:21:01.210

The One Row Issue

Option Explicit

Sub RunMerge()

    ' Define constants.
    Const FirstRow As Long = 2
    Const srcColumn As Variant = "A" ' e.g. "A" or 1

    ' Define Source Column Range.
    With ThisWorkbook.Worksheets("Patients")
        Dim rng As Range
        Set rng = .Columns(srcColumn).Find("*", , xlValues, , , xlPrevious)
        If rng Is Nothing Then Exit Sub
        If rng.Row < FirstRow Then Exit Sub
        Set rng = .Range(.Cells(FirstRow, srcColumn), rng)
    End With

    ' Write Source Range to Source Array.
    Dim Patients As Variant ' Not necessary, but considered correct.
    If rng.Rows.Count = 1 Then
        ReDim Patients(1 To 1, 1 To 1)
        Patients(1, 1) = rng.Value
    Else
        Patients = rng.Value
    End If
    Set rng = Nothing

    ' Either:
    Debug.Print "For Next"
    Dim i As Long
    For i = 1 To UBound(Patients)
        Debug.Print Patients(i, 1)
    Next i
    ' or:
    Debug.Print "For Each"
    Dim Patient As Variant
    For Each Patient In Patients
        Debug.Print Patient
    Next Patient

End Sub

Handling an array with one element in a for each loop

1 Answers1

The One Row Issue