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.
 
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.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
In that case how about
Code:
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
 
Upvote 0
Disregard the Col C Date issue after the section. I change the macro to delete the date cell earlier in the process.
 
Upvote 0
In that case how about
Code:
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.
 
Upvote 0
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).
 
Upvote 0
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.
Code:
<>
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.
 
Last edited:
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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