VBA - Identify Begin/End of Cell Ranges

[ajocius]

Group,
I have a spreadsheet that changes weekly. The spreadsheet can have
over a 1000 rows or just a few hundred. In column D, a cell may be
blank or if there is an entry will have a text value with a suffix
..checksum, like regioncode.checksum or rds.checksum. The prefix before
the (.) dot will always be lumped together once there is an entry. In
otherword, rds.checksum will never appear again after cell D7 once its
initial incidence (D3) is found, regioncode.checksum will never appear
again after D20 once its first incidence (D11) is found below. I want
to show on another worksheet called "Summary", the name of the variable
(ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its
ending row (7) in cell C2 and so on for each group of .checksum
entries. Any help here would be awesome.

Cell:Contents
D1: < Blank>
D2: < Blank>
D3: rds.checksum
D4: rds.checksum
D5: rds.checksum
D6: rds.checksum
D7: rds.checksum
D8: < Blank >
D9: < Blank >
D10: < Blank >
D11: regioncode.checksum
D12: regioncode.checksum
D13: regioncode.checksum
D14: regioncode.checksum
D15: regioncode.checksum
D16: regioncode.checksum
D17: regioncode.checksum
D18: regioncode.checksum
D19 regioncode.checksum
D20: regioncode.checksum
D21: < Blank >
D22: < Blank >
D23: < Blank >
D24: < Blank >
etc...........

Tony

 

[Tom Ogilvy]

will you have a list of the unique checksums in the summary sheet and you
want formulas to calculate the locations, or do you want a macro to build
the entire summary sheet include building the list of unique checksums?

 

[Ian]

Try this. Change Sheet1 to suit your source sheet name.
Sub findvariable()
rd = 2
For r = 2 To 1000
If Worksheets("Sheet1").Cells(r - 1, 4) = "" And
Worksheets("Sheet1").Cells(r, 4) <> "" Then
Worksheets("Summary").Cells(rd, 1).Value =
Worksheets("Sheet1").Cells(r, 4).Value
Worksheets("Summary").Cells(rd, 2) = Worksheets("Sheet1").Cells(r,
4).Address
End If
If Worksheets("Sheet1").Cells(r - 1, 4) <> "" And
Worksheets("Sheet1").Cells(r, 4) = "" Then
Worksheets("Summary").Cells(rd, 3) = Worksheets("Sheet1").Cells(r,
4).Address
rd = rd + 1
End If
Next r
End Sub

 

[ajocius]

Tom,
I just need a list built with the unique name, and beginning/ending
rows. Everything else is done. What makes this problem so difficult
for me is that the prefix to the .checksum can be different from
Worksheet to Worksheet. Fortunately, the group of something.checksum
will always be unique in the spreadsheet and will be listed
consecutively. I hope that explains it.

Tony

 

[ajocius]

Probably the best way to describe this is to do a print screen of
portion of the spreadsheet. In the screen print, I want to know th
range from D1 through D19 Cal_Align_EE.checksum and though you can'
see all of Cal_EE.checksum its range. The number of ranges fro
spreadsheet to spreadsheet can vary from just a few to several dozen.
Note that the names can change from spreadsheet to spreadsheet in th
prefix. The suffix post decimal is always the same (checksum). Th
result I'm searching for would look like below:

A B C
1 Cal_EE_Align.checksum D1 D19

2 Cal_EE.checksum D23 D25
3 Something.checksum D45 D55
4 Somethingagain.checksum D132 D199
5 ..........etc

Remember that the ranges will be different from spreadsheet t
spreadsheet and the names in column A will be different fro
spreadsheet to spreadsheet. The only common thing from checksum grou
to checksum group is the .checksum suffix.

Is this a little clearer. Many thanks if you can assist me on thi
one.

Ton

+-------------------------------------------------------------------
|Filename: PrintScreenOfProblem.gif
|Download: http://www.excelforum.com/attachment.php?postid=3850
+-------------------------------------------------------------------

 

[Tom Ogilvy]

This article is about building a unique list of items and placing them in a
listbox. You can alter this to place them in a worksheet (and otherwise
modify the code to ignore blank cells in you "AllCell" range).


If you can't figure out how to use it and Ian suggestion doesn't help, then
post back.

 

[Arishy]

Tom

Do you have a link for
<<This article is about building a unique list >> Many thanks