Multiple block sorting

[rjplante]

I have a block of cells from column A - H that can have a variable number of rows. The data set can also have multiple blocks of data separated by a single blank row. I need to have a macro that will start with row 3 and grab the first block from row 3 to the bottom of the block based on the data in column A, and then sort the data based on the values in Column E. Once that block is sorted, I need it to loop X number of times. I have cell BG1, that displays the number of blocks in the full table. I need the macro to loop through the sort protocol the total number displayed in cell BG1.

All of the cell block have the same number of rows, so if you were to go to cell A3 and use xlEndDown, you would have the total number of rows in each block. You could define that number of rows as a variable and use that in the loop.

I don't know how to write this macro to get through the sorting easily enough.

Please and thank you for any assistance with this.

 

[Fluff]

Is column A hard values, or formulae?
Also does each block have a header row?

 

[rjplante]

Yes Column A is hard data. There are headers, but they span two rows. I have the top of the table pictured below. I need the second start date as the one to be sorted on. Column E.

	Plan	Plan	Plan	Actual	Actual	Actual
JOB #	Start Date	# working days	End Date	Start Date	# working days	End Date	Column Header
444	22-Apr-2019	69	25-Jul-2019	22-Apr-2019	69	25-Jul-2019	Design
888	8-Jul-2019	168	26-Feb-2020	8-Jul-2019	168	26-Feb-2020	Electrical

 

[Fluff]

Do you have those header rows between each block of data, or just in rows 1 & 2?

 

[rjplante]

No there is no header row between blocks of data, just a single blank row.

 

[Fluff]

In that case, try

Sub rjplante()
   Dim Rng As Range
   
   For Each Rng In Range("A3", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Resize(, 8).Sort Rng.Offset(, 4), xlAscending, , , , , , xlNo
   Next Rng
End Sub

 

[rjplante]

That is so nice and short and it works perfect. Thanks for the nice little tidy bit of code.

 

[Fluff]

You're welcome & thanks for the feedback