Select Section between blank rows

[srschicago]

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.

 

[DanteAmor]

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 />

 

[srschicago]

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

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

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

</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

 

[DanteAmor]

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

 

[Fluff]

Is your data like

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	District	Ward	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef
2	Bedfordshire
3	Luton	Biscot	LU1 1HP	Yes	51.884074	-0.424657	508524	221785	TL085217
4	Luton	South	LU1 1WF	No	51.879978	-0.422926	508653	221332	TL086213
5	Luton	South	LU1 3RG	Yes	51.868744	-0.417989	509020	220090	TL090200
6	Luton	Farley	LU1 5TT	No	51.87459	-0.430204	508165	220722	TL081207
7	Luton	Round Green	LU2 7JH	Yes	51.89104	-0.407745	509671	222585	TL096225
8
9
10	Berkshire
11	Reading	Abbey	RG1 1UQ	No	51.460727	-0.974799	471321	174026	SU713740
12	Reading	Katesgrove	RG1 2PY	Yes	51.447595	-0.971237	471589	172569	SU715725
13	Reading	Abbey	RG1 3EQ	Yes	51.455808	-0.958401	472468	173495	SU724734
14	Reading	Redlands	RG1 4NX	Yes	51.453604	-0.957343	472545	173251	SU725732
15	Reading	Minster	RG1 6LB	Yes	51.448354	-0.987207	470478	172638	SU704726
16
17	Cambridgeshire
18	Cambridge	Abbey	CB1 0EY	No	52.209345	0.148231	546881	258966	TL468589
19	Cambridge	Market	CB1 1HW	Yes	52.207754	0.134806	545969	258762	TL459587
20	Cambridge	Trumpington	CB1 2EH	Yes	52.199596	0.130493	545701	257846	TL457578
21	Cambridge	Petersfield	CB1 2TF	No	52.201419	0.137561	546178	258063	TL461580
22	Cambridge	Romsey	CB1 3HN	Yes	52.202139	0.15384	547288	258176	TL472581
23	Cambridge	Coleridge	CB1 4HF	No	52.18618	0.153244	547300	256400	TL473564
List

Where you need to select the areas in green

 

[DanteAmor]

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>

 

[srschicago]

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!

 

[Fluff]

How about

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

 

[Rick Rothstein]

How about

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)...

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

 

[srschicago]

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)