Splitting Data into Districts

[DPChristman]

I have a spreadsheet with approximately 1,000 rows.

Each row represents a store location

These 1,000 rows are divided into approximately 310 numbered districts (3 digit numbers).

At the end of each district I need to have a blank row, followed by a row with the district totals, followed by another blank row.

I am not worried about the math part for the districts, I have that figured out.

What I am looking for is a way to enter three blank rows between each district, without having to do it manually 300+ times.

Is there a macro or script that can accomplish this?

 

[JoeMo]

Awesome! Worked perfectly. Thanks

You are welcome - thanks for the reply.

 

[DPChristman]

Help! I found a flaw in the formula.

Now that I am putting the formula into practical use, I found a flaw.

Prior to launching the macro, I sorted the data by the first three columns (A, B, and then C or Region, District, and then store)

Everything sorted properly.

However, after running the macro, the columns were resorted somehow, and the regions are no longer in order.

I think I know why. The correlation of Region to District is not 100 % logical.

For example, Region 10 includes districts 101, 102, 103, 104, and 105. However, it also district 133

It would make sense if district 133 was in region 13, but it is not

Likewise, districts 106-109 are in region 113, as are 110, 147, 148, 149, 150, 151, and 152

After the macro is run, the alignment of region to district is correct, but that district is physically located in the wrong are of the spreadsheet, placing Region 10 district 133 in the area of the spreadsheet where the Region 13 data resides.

This makes the district and regional subtotals difficult and complicated to follow.

That is a very complicated explanation, I realize, but I am just figuring it out as I go.

The short version is that something in this macro is instructing the data to sort by district (column B)

In reality it should sort first by Region (A), then District (B), and then Store (C)

I think this is referenced somewhere in this instruction:
Range("A8").CurrentRegion.Sort key1:=[b2], order1:=xlAscending, Header:=xlYes

However, I don't know how to fix it. I can speculation that I need to have a Sort key1, Sort key2, Sort key3

unfortunately, that is waaay above my skill level.

Can you help with this?

 

[DPChristman]

Yes, the idea is Regions first (col A), followed by Districts (col B), followed by Store numbers (col C)

That way, there can be a District Total at the end of each district, and a region total at the end of each region.

 

[DPChristman]

I seem to have lost your last response somewhere

 

[DPChristman]

Please look at the notes from last night.

I though you had responded but I think I am wrong

I am having a problem with sorting as a result of the macro. See post #22 in this thread

 

[DPChristman]

Help! This Macro is suddenly not working. The spreadsheet was created in 2016, and used for two years after that. This year when I went to run it I click on run on the macro page, and nothing happens. The spreadsheet format is the same, as is the Macro (below):

Sub ThreeRowsBetween()

Dim i As Long

Application.ScreenUpdating = False

With Rows(7)

.Insert

.Offset(-1, 0).RowHeight = 1

End With

Range("A8").CurrentRegion.Sort key1:=[b2], order1:=xlAscending, Header:=xlYes

For i = Range("B8:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count To 10 Step -1

If Cells(i, "B") <> Cells(i - 1, "B") Then

Cells(i, "B").Resize(3, 1).EntireRow.Insert

End If

Next i

Rows(7).Delete

Application.ScreenUpdating = True

End Sub

<colgroup><col></colgroup><tbody>
</tbody>