+ Reply to Thread
Results 1 to 7 of 7

VBA - Identify Begin/End of Cell Ranges

  1. #1
    Forum Contributor
    Join Date
    12-22-2004
    Location
    Kokomo, Indiana
    Posts
    236

    Question VBA - Identify Begin/End of Cell Ranges

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: VBA - Identify Begin/End of Cell Ranges

    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?

    --
    Regards,
    Tom Ogilvy

    "ajocius" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >
    >
    > --
    > ajocius
    > ------------------------------------------------------------------------
    > ajocius's Profile:

    http://www.excelforum.com/member.php...o&userid=17695
    > View this thread: http://www.excelforum.com/showthread...hreadid=470579
    >




  3. #3
    Ian
    Guest

    Re: VBA - Identify Begin/End of Cell Ranges

    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


    --
    Ian
    --
    "ajocius" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >
    >
    > --
    > ajocius
    > ------------------------------------------------------------------------
    > ajocius's Profile:
    > http://www.excelforum.com/member.php...o&userid=17695
    > View this thread: http://www.excelforum.com/showthread...hreadid=470579
    >




  4. #4
    Forum Contributor
    Join Date
    12-22-2004
    Location
    Kokomo, Indiana
    Posts
    236

    Exclamation

    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

  5. #5
    Forum Contributor
    Join Date
    12-22-2004
    Location
    Kokomo, Indiana
    Posts
    236

    Question

    Probably the best way to describe this is to do a print screen of a portion of the spreadsheet. In the screen print, I want to know the range from D1 through D19 Cal_Align_EE.checksum and though you can't see all of Cal_EE.checksum its range. The number of ranges from spreadsheet to spreadsheet can vary from just a few to several dozen. Note that the names can change from spreadsheet to spreadsheet in the prefix. The suffix post decimal is always the same (checksum). The 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 to spreadsheet and the names in column A will be different from spreadsheet to spreadsheet. The only common thing from checksum group to checksum group is the .checksum suffix.

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

    Tony
    Attached Images Attached Images

  6. #6
    Tom Ogilvy
    Guest

    Re: VBA - Identify Begin/End of Cell Ranges

    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.

    --
    Regards,
    Tom Ogilvy

    "ajocius" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Probably the best way to describe this is to do a print screen of a
    > portion of the spreadsheet. In the screen print, I want to know the
    > range from D1 through D19 Cal_Align_EE.checksum and though you can't
    > see all of Cal_EE.checksum its range. The number of ranges from
    > spreadsheet to spreadsheet can vary from just a few to several dozen.
    > Note that the names can change from spreadsheet to spreadsheet in the
    > prefix. The suffix post decimal is always the same (checksum). The
    > 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 to
    > spreadsheet and the names in column A will be different from
    > spreadsheet to spreadsheet. The only common thing from checksum group
    > to checksum group is the .checksum suffix.
    >
    > Is this a little clearer. Many thanks if you can assist me on this
    > one.
    >
    > Tony
    >
    >
    > +-------------------------------------------------------------------+
    > |Filename: PrintScreenOfProblem.gif |
    > |Download: http://www.excelforum.com/attachment.php?postid=3850 |
    > +-------------------------------------------------------------------+
    >
    > --
    > ajocius
    > ------------------------------------------------------------------------
    > ajocius's Profile:

    http://www.excelforum.com/member.php...o&userid=17695
    > View this thread: http://www.excelforum.com/showthread...hreadid=470579
    >




  7. #7
    Arishy
    Guest

    Re: VBA - Identify Begin/End of Cell Ranges

    Tom

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1