Hello Excel Group,

I have a question (Excel 2003 and VBA). I have a worksheet with a
column of dates,times, and values.
the first col has a date (13-Sept-2007), the second col has a time
(12:00:00), and the third col a value (like 10).

I get a worksheet provided with data (date, time, values, etc) This is
test data.

The dates remain the same for a few rows, then the date can change,
remains the same for x rows, and the changes, etc. etc.

I do not want to do any programming on the worksheet itself, but all
in vba - until I write something to the worksheet.

Currently I copy the date column into an array that holds all the
dates.
I want to read down the column and note when the date changes, then I
want to define a range.
for example - I can have a column with 20 rows of dates, the date
stays the same for, let's say 11 rows,
and then changes. I then want to define a range for each date. So
the first range would be (keeping the date the same) from a2:a11, the
second range would be a12:a20. easy with only 20 rows, and one
different date. But.... the data can stretch for a period of a few
weeks. I want to count the total number of "ranges" in my column, for
each date change implies a new range. I was using a for loop, grab
the first date (easy, col a, row 2) - and compare each val in the
array, when it changes, then
I know I have at least 2 ranges, etc. My problem comes when i get to
the date that changes (a12 in the simple case) - i need to do the same
type of search down the rest of the array and look for the next date
change and do it all over again. I am caught in a loop!

Any suggestions on how to find the places where the date changes
(start for a new range) in an array? maybe some sort of cutting the
array each time a date changes??

Thanks for any info you might suggest,

eholz1

Have you thought about using a collection of ranges?
Sub RangeCollection()
Dim MyCollection As Collection
Dim MyRange As Range
Dim MyRange2 As Range
Dim cnt As Long

Set MyCollection = New Collection
Set MyRange = Range("A2")
cnt = 3
Do
Set MyRange2 = Range("A" & cnt)
If MyRange2 < MyRange2.Offset(-1) Then
'Date has changed add to collection
MyCollection.Add MyRange
Set MyRange = MyRange2
Else
'Date is the same and to existing range
Set MyRange = Union(MyRange, MyRange2)
End If
cnt = cnt + 1
Loop Until MyRange2.Offset(1) = ""
MyCollection.Add MyRange
Debug.Print MyCollection.Count
For Each MyRange In MyCollection
Debug.Print MyRange.Address
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"eholz1" wrote:

Hello Excel Group,

I have a question (Excel 2003 and VBA). I have a worksheet with a
column of dates,times, and values.
the first col has a date (13-Sept-2007), the second col has a time
(12:00:00), and the third col a value (like 10).

I get a worksheet provided with data (date, time, values, etc) This is
test data.

The dates remain the same for a few rows, then the date can change,
remains the same for x rows, and the changes, etc. etc.

I do not want to do any programming on the worksheet itself, but all
in vba - until I write something to the worksheet.

Currently I copy the date column into an array that holds all the
dates.
I want to read down the column and note when the date changes, then I
want to define a range.
for example - I can have a column with 20 rows of dates, the date
stays the same for, let's say 11 rows,
and then changes. I then want to define a range for each date. So
the first range would be (keeping the date the same) from a2:a11, the
second range would be a12:a20. easy with only 20 rows, and one
different date. But.... the data can stretch for a period of a few
weeks. I want to count the total number of "ranges" in my column, for
each date change implies a new range. I was using a for loop, grab
the first date (easy, col a, row 2) - and compare each val in the
array, when it changes, then
I know I have at least 2 ranges, etc. My problem comes when i get to
the date that changes (a12 in the simple case) - i need to do the same
type of search down the rest of the array and look for the next date
change and do it all over again. I am caught in a loop!

Any suggestions on how to find the places where the date changes
(start for a new range) in an array? maybe some sort of cutting the
array each time a date changes??

Thanks for any info you might suggest,

eholz1

On Sep 18, 1:22 pm, Charles Chickering
wrote:
Have you thought about using a collection of ranges?
Sub RangeCollection()
Dim MyCollection As Collection
Dim MyRange As Range
Dim MyRange2 As Range
Dim cnt As Long

Set MyCollection = New Collection
Set MyRange = Range("A2")
cnt = 3
Do
Set MyRange2 = Range("A" & cnt)
If MyRange2 < MyRange2.Offset(-1) Then
'Date has changed add to collection
MyCollection.Add MyRange
Set MyRange = MyRange2
Else
'Date is the same and to existing range
Set MyRange = Union(MyRange, MyRange2)
End If
cnt = cnt + 1
Loop Until MyRange2.Offset(1) = ""
MyCollection.Add MyRange
Debug.Print MyCollection.Count
For Each MyRange In MyCollection
Debug.Print MyRange.Address
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."

"eholz1" wrote:
Hello Excel Group,

I have a question (Excel 2003 and VBA). I have a worksheet with a
column of dates,times, and values.
the first col has a date (13-Sept-2007), the second col has a time
(12:00:00), and the third col a value (like 10).

I get a worksheet provided with data (date, time, values, etc) This is
test data.

The dates remain the same for a few rows, then the date can change,
remains the same for x rows, and the changes, etc. etc.

I do not want to do any programming on the worksheet itself, but all
in vba - until I write something to the worksheet.

Currently I copy the date column into an array that holds all the
dates.
I want to read down the column and note when the date changes, then I
want to define a range.
for example - I can have a column with 20 rows of dates, the date
stays the same for, let's say 11 rows,
and then changes. I then want to define a range for each date. So
the first range would be (keeping the date the same) from a2:a11, the
second range would be a12:a20. easy with only 20 rows, and one
different date. But.... the data can stretch for a period of a few
weeks. I want to count the total number of "ranges" in my column, for
each date change implies a new range. I was using a for loop, grab
the first date (easy, col a, row 2) - and compare each val in the
array, when it changes, then
I know I have at least 2 ranges, etc. My problem comes when i get to
the date that changes (a12 in the simple case) - i need to do the same
type of search down the rest of the array and look for the next date
change and do it all over again. I am caught in a loop!

Any suggestions on how to find the places where the date changes
(start for a new range) in an array? maybe some sort of cutting the
array each time a date changes??

Thanks for any info you might suggest,

eholz1

Hello Group and Charles
I knew there was a more "elegant" way to do this.
I will try this out.
Thanks very much for this tip,

ehoz1