Better to user ReDim ?

[bassman71]

Hello, the following seems like I could use ReDim statement. I am looping/counting through a vertical range and stopping when the value in the cell to the left = a particular time, then using that count I set the range. (This is part of a bigger loop) The challenge is the Count will vary with each iteration. Is there any way using redim to assess the number in range?

[VBA]Dim TestOffset As Long, TimeT As Long ' I've reformatted time as integer

Do Until ActiveCell(1, 0).Value > (TimeT + TestOffset) - 1
ActiveCell.Offset(1, 0).Select
Count = Count + 1
Loop
ActiveCell.Offset(-Count, 0).Select
Set rng1 = Range(ActiveCell, ActiveCell.Offset(Count))[/VBA]

Many thanks.......RO

[Zack Barresse]

Hi there,

ReDim is for array's, which you are not using here. You could use an array here. You could also not use the Select statement. I can't see all of your code, but maybe you could post a real world example of what you are trying to do along with the full code. Maybe we could get you some working code.

[mdmackillop]

Something like
[vba]Sub SetRange()
Dim TestOffset As Long, TimeT As Long ' I've reformatted time as integer
Dim rng1 As Range, cel as Range
For Each cel In Range(ActiveCell, ActiveCell.End(xlDown))
If cel > (TimeT + TestOffset) - 1 Then Exit For
Next
Set rng1 = Range(ActiveCell, cel)
rng1.Select
End Sub[/vba]
or to check the column to the left for values (if that's what you mean)
[VBA]
For Each cel In Range(ActiveCell, ActiveCell.End(xlDown))
If cel.Offset(, -1) > (TimeT + TestOffset) - 1 Then Exit For
Next

[/VBA]