Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Select Section between blank rows

  1. #1
    Board Regular
    Join Date
    Apr 2017
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Select Section between blank rows

    Hey Team,
    I have a worksheet with data in columns A thu E that is broken into sections separated by blank rows and a unique "Title" in column A starting each section. I need to select the col A range of a section so that I can format each column differently. CurrentRegion doesn't do it because I need to resize and offset the selection.
    Thanks in advance for your help.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Select Section between blank rows

    In the following example you can explain what you need to select.


     ABCDE
    1Title section1b1c1d1e1
    2 b2c2d2e2
    3 b3c3d3e3
    4 b4c4d4e4
    5Title section2b5c5d5e5
    6 b6c6d6e6
    7 b7c7d7e7
    8 b8c8d8e8
    9Title section3b9c9d9e9
    10 b10c10d10e10
    11 b11c11d11e11
    12 b12c12d12e12


    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Apr 2017
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Section between blank rows

    Quote Originally Posted by DanteAmor View Post
    In the following example you can explain what you need to select.


    A B C D E
    1 Title section1 b1 c1 d1 e1
    2 b2 c2 d2 e2
    3 b3 c3 d3 e3
    4 b4 c4 d4 e4
    5 Title section2 b5 c5 d5 e5
    6 b6 c6 d6 e6
    7 b7 c7 d7 e7
    8 b8 c8 d8 e8
    9 Title section3 b9 c9 d9 e9
    10 b10 c10 d10 e10
    11 b11 c11 d11 e11
    12 b12 c12 d12 e12

    The data is also in column A. There is no data in b,c,d,e of the title row. There are entirely blank rows between each section. I need to select the data range directly below the title in Column A and ending with the next blank row. I am sorry for the confusion
    Last edited by srschicago; Apr 20th, 2019 at 12:37 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Select Section between blank rows

    I'm still confused, could you explain with data, what do you have and what do you need to select?
    Regards Dante Amor

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    21,861
    Post Thanks / Like
    Mentioned
    357 Post(s)
    Tagged
    36 Thread(s)

    Default Re: Select Section between blank rows

    Is your data like

    Excel 2013/2016
    ABCDEFGHI
    1DistrictWardPostcodeIn Use?LatitudeLongitudeEastingNorthingGridRef
    2Bedfordshire
    3LutonBiscotLU1 1HPYes51.884074-0.424657508524221785TL085217
    4LutonSouthLU1 1WFNo51.879978-0.422926508653221332TL086213
    5LutonSouthLU1 3RGYes51.868744-0.417989509020220090TL090200
    6LutonFarleyLU1 5TTNo51.87459-0.430204508165220722TL081207
    7LutonRound GreenLU2 7JHYes51.89104-0.407745509671222585TL096225
    8
    9
    10Berkshire
    11ReadingAbbeyRG1 1UQNo51.460727-0.974799471321174026SU713740
    12ReadingKatesgroveRG1 2PYYes51.447595-0.971237471589172569SU715725
    13ReadingAbbeyRG1 3EQYes51.455808-0.958401472468173495SU724734
    14ReadingRedlandsRG1 4NXYes51.453604-0.957343472545173251SU725732
    15ReadingMinsterRG1 6LBYes51.448354-0.987207470478172638SU704726
    16
    17Cambridgeshire
    18CambridgeAbbeyCB1 0EYNo52.2093450.148231546881258966TL468589
    19CambridgeMarketCB1 1HWYes52.2077540.134806545969258762TL459587
    20CambridgeTrumpingtonCB1 2EHYes52.1995960.130493545701257846TL457578
    21CambridgePetersfieldCB1 2TFNo52.2014190.137561546178258063TL461580
    22CambridgeRomseyCB1 3HNYes52.2021390.15384547288258176TL472581
    23CambridgeColeridgeCB1 4HFNo52.186180.153244547300256400TL473564

    List





    Where you need to select the areas in green
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Select Section between blank rows

    You need to format each column for example:

     ABCDE
    1Title section1    
    2a2b2c2d2e2
    3a3b3c3d3e3
    4a4b4c4d4e4
    5     
    6Title section2    
    7a7b6c6d6e6
    8a8b7c7d7e7
    9a9b8c8d8e8
    10     
    11Title section3    
    12a10b10c10d10e10
    13a11b11c11d11e11
    14a12b12c12d12e12
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Apr 2017
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Section between blank rows

    The example with different color columns is what my data looks like. The first row after the title is a header row, but as long as I get the colored range in column A, I can do the rest. Thanks for your help!

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    21,861
    Post Thanks / Like
    Mentioned
    357 Post(s)
    Tagged
    36 Thread(s)

    Default Re: Select Section between blank rows

    How about
    Code:
    Sub srschicago()
       Dim Rng As Range
       For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
          Rng.Offset(2).Resize(Rng.Count - 2).Interior.Color = vbYellow
       Next Rng
    End Sub
    If you want to include the header row change the 2s to 1s
    Last edited by Fluff; Apr 20th, 2019 at 02:45 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,358
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Select Section between blank rows

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub srschicago()
       Dim Rng As Range
       For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
          Rng.Offset(2).Resize(Rng.Count - 2).Interior.Color = vbYellow
       Next Rng
    End Sub
    If you want to include the header row change the 2s to 1s
    If that does what the OP wants, then I think this will also (change the one 2 to a 1 to include the headers)...
    Code:
    Sub srschicago2()
      Range("A:A").SpecialCells(xlConstants).Offset(2).Interior.Color = vbYellow
      Range("A:A").SpecialCells(xlBlanks).Interior.Color = xlNone
      Range("A:A").SpecialCells(xlBlanks).Offset(1).Interior.Color = xlNone
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular
    Join Date
    Apr 2017
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Section between blank rows

    I guess I am not being clear enough. I need to select the column A range immediately below the Section 2 title cell up to the next blank cell in Col A
    I am then going to offset the range to select column B and gray fill those cells.
    Then offset and resize to include all columns and apply borders to Col A:E in the selected rows. (All Section 2 data)
    Note: There is a date in the Col C cell of the row immediately below this data that I want to delete as well (to make the entire row truly blank).
    So I need the address of the last row of the section two Col A data as well (or the blank cell below it, which will be the row I clear of the date in Col C)
    Last edited by srschicago; Apr 20th, 2019 at 04:24 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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