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]

It would be more effective if you help us with an image of what you have before the formats and another image with what you expect of result.

 

[Fluff]

In that case how about

Sub srschicago()
   Dim Rng As Range
   For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
      Rng.Offset(1, 1).Resize(Rng.Count - 1).Interior.Color = 12566463
      Rng.Offset(1).Resize(Rng.Count - 1, 5).Borders.Weight = xlMedium
   Next Rng
End Sub

 

[srschicago]

Disregard the Col C Date issue after the section. I change the macro to delete the date cell earlier in the process.

 

[srschicago]

In that case how about

Sub srschicago()
   Dim Rng As Range
   For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
      Rng.Offset(1, 1).Resize(Rng.Count - 1).Interior.Color = 12566463
      Rng.Offset(1).Resize(Rng.Count - 1, 5).Borders.Weight = xlMedium
   Next Rng
End Sub

Thanks Fluff, but your code formats all of the sections with data in Col A and I do not want this. Only the section attached to the title cell I have selected. I would also like to sort the data by Col A so getting the range selected seems necessary.

 

[Rick Rothstein]

Thanks Fluff, but your code formats all of the sections with data in Col A and I do not want this. Only the section attached to the title cell I have selected. I would also like to sort the data by Col A so getting the range selected seems necessary.

Please read Message #11 ... complying with it will remove the necessity to keep guessing at what your data looks like and what you are ultimately trying to do with it (as they say, a picture is worth a thousand words).

 

[srschicago]

I installed the MrExcel HTML Maker and added the add-in to Excel. The buttons to select options were activating, but the button to create the HTML image would not. I gave up on it because of time.
Speaking of your time, thank you all for yours. I solved my problem using CurrentRegion after all.
Basically I have a series of tables vertically aligned and separated by blank rows. Each table has a Title cell above it.
Here is the code I used to format the 2nd table. The Title cell is where I start in for the macro.

<>
Sub rangeselect()
    ActiveCell.Select
    Selection.CurrentRegion.Offset(1).Resize(Selection.CurrentRegion.Rows.Count - 1).Select
'Format borders....
    Selection.CurrentRegion.Offset(1, 1).Resize(Selection.CurrentRegion.Rows.Count - 1, _
    Selection.CurrentRegion.Columns.Count - 4).Select
'Fill Column B with gray...
    
End Sub
[END]<>
Sorry that I don't remember the correct code posing syntax.

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[DanteAmor]

If you have problems with MrExcel HTML Maker, you can install Excel_Jeanie_Html

https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813

Or you can upload images to dropbox.