Reading a Variable Length Range

[OldRookie]

I have a range of cells, Range(A1:Z1), that will contain nonzero numerical entries. Entries will be placed in the cells starting in A1 and progressing towards Z1 with no empty cells in between. The number of entries will vary; empty cells will be at the end. I want to read the cells with entries into a VBA array and count how many entries were read.

I believe reading with On Error can do it but I’m under the impression there may be something more succinct/direct/specific. I’m sure this has been answered in previous threads. Unfortunately, my searches for them were fruitless. Would someone please direct to a couple of threads that discuss this topic.
Thank you.

 

[BigBeachBananas]

How about this?

Sub ReadNonEmptyCellsToArray()
    Dim rng As Range
    Dim cell As Range
    Dim dataArray() As Variant
    Dim count As Long


    Set rng = Range("A1:Z1")
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            count = count + 1
            ReDim Preserve dataArray(1 To count)
            dataArray(count) = cell.Value
        End If
    Next cell

End Sub

 

[OldRookie]

BigBeachBananas,
Thank you. I’ll need to study it and research out the commands. They’re new to me.

Thank you very much.

 

[kevin9999]

Another option, assumes nothing to the right of Z1, and code run on the active sheet.

Option Explicit
Sub Get_Array()
    Dim arr, i As Long
    
    'Fill array "arr" with values from filled cells in row 1
    arr = Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    
    'get the count of array elements
    i = UBound(arr, 2)
    MsgBox i & " entries were read"

End Sub

 

[OldRookie]

Kevin9999,
The use your code assumes is exactly what I plan on doing. Zero out A1:Z1, enter an undetermined number of entries, push a “calculate” button on the active sheet, wait for the results while sipping a cup of coffee, have them appear the same active sheet. It’s a singular purpose spreadsheet.
Thank you

 

[kevin9999]

Kevin9999,
The use your code assumes is exactly what I plan on doing. Zero out A1:Z1, enter an undetermined number of entries, push a “calculate” button on the active sheet, wait for the results while sipping a cup of coffee, have them appear the same active sheet. It’s a singular purpose spreadsheet.
Thank you

So... does it achieve what you're looking for? Also, this comment:

wait for the results while sipping a cup of coffee

worries me a bit. What calculations are taking that long? Are you able to share your file via Google Drive, Dropbox or similar file sharing platform?

 

[OldRookie]

I haven’t written the actual code yet. While there could potentially be a noticeable run time, my reference to sipping coffee was jestfully alluding to the simple manner in which I intend to use the spreadsheet.