Select Section between blank rows

srschicago

Board Regular
Joined
Apr 14, 2017
Messages
59
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
In the following example you can explain what you need to select.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:96.95px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Title section1</td><td >b1</td><td >c1</td><td >d1</td><td >e1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >b2</td><td >c2</td><td >d2</td><td >e2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >b3</td><td >c3</td><td >d3</td><td >e3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >b4</td><td >c4</td><td >d4</td><td >e4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Title section2</td><td >b5</td><td >c5</td><td >d5</td><td >e5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >b6</td><td >c6</td><td >d6</td><td >e6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >b7</td><td >c7</td><td >d7</td><td >e7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >b8</td><td >c8</td><td >d8</td><td >e8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Title section3</td><td >b9</td><td >c9</td><td >d9</td><td >e9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >b10</td><td >c10</td><td >d10</td><td >e10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >b11</td><td >c11</td><td >d11</td><td >e11</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >b12</td><td >c12</td><td >d12</td><td >e12</td></tr></table> <br /><br />
 
Upvote 0
In the following example you can explain what you need to select.


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

<tbody>
</tbody>
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:
Upvote 0
I'm still confused, could you explain with data, what do you have and what do you need to select?
 
Upvote 0
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
 
Upvote 0
You need to format each column for example:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:96.95px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Title section1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">a2</td><td style="background-color:#92d050; ">b2</td><td style="background-color:#00b0f0; ">c2</td><td style="background-color:#7030a0; ">d2</td><td style="background-color:#f79646; ">e2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; ">a3</td><td style="background-color:#92d050; ">b3</td><td style="background-color:#00b0f0; ">c3</td><td style="background-color:#7030a0; ">d3</td><td style="background-color:#f79646; ">e3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; ">a4</td><td style="background-color:#92d050; ">b4</td><td style="background-color:#00b0f0; ">c4</td><td style="background-color:#7030a0; ">d4</td><td style="background-color:#f79646; ">e4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Title section2</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffff00; ">a7</td><td style="background-color:#92d050; ">b6</td><td style="background-color:#00b0f0; ">c6</td><td style="background-color:#7030a0; ">d6</td><td style="background-color:#f79646; ">e6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff00; ">a8</td><td style="background-color:#92d050; ">b7</td><td style="background-color:#00b0f0; ">c7</td><td style="background-color:#7030a0; ">d7</td><td style="background-color:#f79646; ">e7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff00; ">a9</td><td style="background-color:#92d050; ">b8</td><td style="background-color:#00b0f0; ">c8</td><td style="background-color:#7030a0; ">d8</td><td style="background-color:#f79646; ">e8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Title section3</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffff00; ">a10</td><td style="background-color:#92d050; ">b10</td><td style="background-color:#00b0f0; ">c10</td><td style="background-color:#7030a0; ">d10</td><td style="background-color:#f79646; ">e10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffff00; ">a11</td><td style="background-color:#92d050; ">b11</td><td style="background-color:#00b0f0; ">c11</td><td style="background-color:#7030a0; ">d11</td><td style="background-color:#f79646; ">e11</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffff00; ">a12</td><td style="background-color:#92d050; ">b12</td><td style="background-color:#00b0f0; ">c12</td><td style="background-color:#7030a0; ">d12</td><td style="background-color:#f79646; ">e12</td></tr></table>
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top