If there a low impact way of searching a colomn for a change.

i.e. Column 5 has 204 rows of "Text1", then 318 rows of "Text3" then 128
rows of "Text2", then 356 rows of "Text3", etc. At run time, I "know" column
5 and how many total rows I have, but I don't know the actual values I may
find or how many of each I'll have.

Fuctionally, I would like something similar to:

Set rngSearchRange = Range(Cells(1,5),Cells(lngLastRow, 5)
strSearchText = rngSearchRange.Cells(1).Value

Set rngHitCell = rngSearchRange .Find(What:= ^strSearchText)
'First cell that
doesn't match

While Not rngHitCell is Nothing
<Do processing on Section upto HitCell

Set rngSearchRange = Range(rngHitCell,Cells(lngLastRow,5)
strSearchText = rngHitCell.Value
Set rngHitCell = rngSearchRange .Find(What:= ^strSearchText)
Wend

Right now, the fastest way seems to be read the column into a variant to
move it into memory, iterate through the array, and process worksheet based
on index values in the variant. This question is not a matter of finding a
way to do what I want, it's about finding a better way.

TIA, Bob

Hi Bob,

I use the following often after the data is placed in 'column 5':

1. Use the advanced data filter to extract all unique values from 'column 5'
to a 'spare' column.
2. Use in column 'spare+1' the somproduct formula to count the number of
ocurrances of the values fixed in 'spare' in column 5. Or with the COUNTIF
function.

I hope you can understand.

Best regards,

Gijs

"INTP56" wrote:

If there a low impact way of searching a colomn for a change.

i.e. Column 5 has 204 rows of "Text1", then 318 rows of "Text3" then 128
rows of "Text2", then 356 rows of "Text3", etc. At run time, I "know" column
5 and how many total rows I have, but I don't know the actual values I may
find or how many of each I'll have.

Fuctionally, I would like something similar to:

Set rngSearchRange = Range(Cells(1,5),Cells(lngLastRow, 5)
strSearchText = rngSearchRange.Cells(1).Value

Set rngHitCell = rngSearchRange .Find(What:= ^strSearchText)
'First cell that
doesn't match

While Not rngHitCell is Nothing
<Do processing on Section upto HitCell

Set rngSearchRange = Range(rngHitCell,Cells(lngLastRow,5)
strSearchText = rngHitCell.Value
Set rngHitCell = rngSearchRange .Find(What:= ^strSearchText)
Wend

Right now, the fastest way seems to be read the column into a variant to
move it into memory, iterate through the array, and process worksheet based
on index values in the variant. This question is not a matter of finding a
way to do what I want, it's about finding a better way.

TIA, Bob

Gijs,

I was hoping for something that didn't involve using a column either. In a
sense, I'm exchanging memory space for Worksheet space.

Another way by adding an extra column is to create a column that increments
if the previous row is the same as the current row, or set to a 1 if not.
Then I can use the .Find command on that column to look for 1's, which is now
a flag that indicates something changed. The advantage of this method over
reading it into a variant is the user can look at the formula and understand
what's happening without having to explain VBA code.

Thanks for your input.

Bob


"Gijs Breedveld" wrote:

Hi Bob,

I use the following often after the data is placed in 'column 5':

1. Use the advanced data filter to extract all unique values from 'column 5'
to a 'spare' column.
2. Use in column 'spare+1' the somproduct formula to count the number of
ocurrances of the values fixed in 'spare' in column 5. Or with the COUNTIF
function.

I hope you can understand.

Best regards,

Gijs

"INTP56" wrote:

If there a low impact way of searching a colomn for a change.

i.e. Column 5 has 204 rows of "Text1", then 318 rows of "Text3" then 128
rows of "Text2", then 356 rows of "Text3", etc. At run time, I "know" column
5 and how many total rows I have, but I don't know the actual values I may
find or how many of each I'll have.

Fuctionally, I would like something similar to:

Set rngSearchRange = Range(Cells(1,5),Cells(lngLastRow, 5)
strSearchText = rngSearchRange.Cells(1).Value

Set rngHitCell = rngSearchRange .Find(What:= ^strSearchText)
'First cell that
doesn't match

While Not rngHitCell is Nothing
<Do processing on Section upto HitCell

Set rngSearchRange = Range(rngHitCell,Cells(lngLastRow,5)
strSearchText = rngHitCell.Value
Set rngHitCell = rngSearchRange .Find(What:= ^strSearchText)
Wend

Right now, the fastest way seems to be read the column into a variant to
move it into memory, iterate through the array, and process worksheet based
on index values in the variant. This question is not a matter of finding a
way to do what I want, it's about finding a better way.

TIA, Bob