Enter blank row after subtotal "Total"

[Guest]

I have a macro for entering a blank row when there is a change in a certain
column. However, after Subtotalling, it recognizes this Subtotal line as a
change in that category, rightfully. Can someone provide a solution/code to
enter a blank row after the Subtotal grouping? Thanks, MissyLovesExcel

 

[Guest]

Hi,

It may be easiest to modify your macro to insert two rows where it finds a
change rather than one. I use code like this myself...

HTH/

 

[Guest]

I didn't write the code so I don't know how to change it. That said,
wouldn't that still recognize the subtotal as a change in that column, I
would get two rows before and after subtotal instead of one? - M

 

[Guest]

It depends on how you structure the code. Mine inserts all the rows first,
then adds the subtotals, so this is no problem. If you post your code maybe I
can help...?

 

[Norman Jones]

Hi Missy,

Try:

'============>>
Public Sub Tester001()
Dim i As Long
Const col As String = "A" '<<==== CHANGE

Application.ScreenUpdating = False

For i = Selection.Rows.Count To 2 Step -1
With Cells(i, col)
If .Value <> .Offset(-1).Value Then
If InStr(1, .Value, "Total", vbTextCompare) = 0 Then
.EntireRow.Insert
End If
End If
End With
Next i

Application.ScreenUpdating = True

End Sub
'<<============

 

[Guest]

Here 'tis:

'============>>
Public Sub Tester001()
Dim rng As Range
Dim rcell As Range
Dim i As Long
Const col As String = "A"

For i = Selection.Rows.Count To 2 Step -1
With Cells(i, col)
If .Value <> .Offset(-1).Value Then
.EntireRow.Insert
End If
End With
Next i
End Sub
'<<============

 

[Norman Jones]

Hi Missy,

See the adaptation in my response to your preceding post.

 

[Guest]

Hello again,

Yes, I think you only need to modify one line. Change:

.EntireRow.Insert

TO:

.EntireRow.Resize(2).Insert Shift:=xlDown

HTH/

 

[Guest]

Sorry Norman, I hope I didn't misguide the OP.

Hi Missy,

See the adaptation in my response to your preceding post.

 

[Norman Jones]

Hi Quartz,

Sorry Norman, I hope I didn't misguide the OP.

No problem!

I think that we have slightly different interpretations of the OP's
intention but this gives the OP the oprtunity to delect the solution which
is most appropriate to her needs - and that may well be your suggestion!