I have a loop that fills an array with values found in ranges:

I look for string "Start," then set the beginning of my range at the cell
that's down 2 cells and to the left 1 cell, then set the END of my range as
the last value before a blank in the column after the beginning of my range:

Set FoundIt = .Find("Start", LookIn:=xlValues, LookAt:=xlPart)

If Not FoundIt Is Nothing Then
FirstAddress = FoundIt.Address

Do

StartRange =
SheetName.Range(FoundIt.Address).Offset(2, -1).Address
EndRange =
SheetName.Range(StartRange).End(xlDown).Address

For Each ACell In SheetName.Range(StartRange,
EndRange)
ReDim Preserve ArrayList(0 To i)
ArrayList(i) = ACell.Value
i = i + 1
Next ACell

Set FoundIt = .FindNext(FoundIt)

Loop While Not FoundIt Is Nothing And FoundIt.Address <
FirstAddress
i = 0
End If

This works great EXCEPT when there is only one value in the StartRange, and
there are a couple blanks after that, then some other value that's not
really supposed to be in my range. So it seems that when I use End(xlDown)
from a StartRange that's the only value (ie. the cell under StartRange is
blank, so really my StartRange and EndRange should be the same cell), Excel
looks for a blank that's after the NEXT value found after the StartRange.

Example: Let's say I found "Start", and I'm now at 2 cells down, 1 to the
left (in the example, Blank means empty cell, not the value "Blank":

A3: val1
A4: val2
A5: val3
A6: Blank
A7: Blank

This sets my StartRange at A3, EndRange at A5, which is correct. Then,
however, (and again, I found "Start" and am now at 2 cells down, 1 over):

A16: val1
A17: Blank
A18: Blank
A19: val2
A20: Blank

This sets my StartRange at A16, but my EndRange at A19 (even though there
are blanks under A16). So this is populating my array with blanks and with
that val2 I don't want.

How can I get this to work so that when my StartRange has the only value,
then EndRange will be the same as StartRange? I can't use offsets on my
current method of setting StartRange and EndRange, because those actually
work perfectly when there ARE more than one value, so an offset would lose
me my first or last value.

Any help on this would be appreciated, and thanks for reading.

On Oct 25, 6:09*am, "CompleteNewb" wrote:
I have a loop that fills an array with values found in ranges:

I look for string "Start," then set the beginning of my range at the cell
that's down 2 cells and to the left 1 cell, then set the END of my range as
the last value before a blank in the column after the beginning of my range:

Set FoundIt = .Find("Start", LookIn:=xlValues, LookAt:=xlPart)

* * * * * * If Not FoundIt Is Nothing Then
* * * * * * * * FirstAddress = FoundIt.Address

* * * * * * * * Do

* * * * * * * * * * StartRange =
SheetName.Range(FoundIt.Address).Offset(2, -1).Address
* * * * * * * * * * EndRange =
SheetName.Range(StartRange).End(xlDown).Address

* * * * * * * * * * * * *For Each ACell In SheetName.Range(StartRange,
EndRange)
* * * * * * * * * * * * * * *ReDim Preserve ArrayList(0 To i)
* * * * * * * * * * * * * * *ArrayList(i) = ACell.Value
* * * * * * * * * * * * * * *i = i + 1
* * * * * * * * * * * * *Next ACell

* * * * * * * * Set FoundIt = .FindNext(FoundIt)

* * * * * * * * Loop While Not FoundIt Is Nothing And FoundIt.Address <
FirstAddress
* * * * * * * * * * * * * * i = 0
* * * * * * End If

This works great EXCEPT when there is only one value in the StartRange, and
there are a couple blanks after that, then some other value that's not
really supposed to be in my range. *So it seems that when I use End(xlDown)
from a StartRange that's the only value (ie. the cell under StartRange is
blank, so really my StartRange and EndRange should be the same cell), Excel
looks for a blank that's after the NEXT value found after the StartRange.

Example: *Let's say I found "Start", and I'm now at 2 cells down, 1 to the
left (in the example, Blank means empty cell, not the value "Blank":

A3: *val1
A4: *val2
A5: *val3
A6: *Blank
A7: *Blank

This sets my StartRange at A3, EndRange at A5, which is correct. *Then,
however, (and again, I found "Start" and am now at 2 cells down, 1 over):

A16: *val1
A17: *Blank
A18: *Blank
A19: *val2
A20: *Blank

This sets my StartRange at A16, but my EndRange at A19 (even though there
are blanks under A16). *So this is populating my array with blanks and with
that val2 I don't want.

How can I get this to work so that when my StartRange has the only value,
then EndRange will be the same as StartRange? *I can't use offsets on my
current method of setting StartRange and EndRange, because those actually
work perfectly when there ARE more than one value, so an offset would lose
me my first or last value.

Any help on this would be appreciated, and thanks for reading.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."