| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter blank row after subtotal "Total"
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
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/ "MissyLovesExcel" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
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...? "MissyLovesExcel" wrote: 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
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 '<<============ --- Regards, Norman "MissyLovesExcel" wrote in message ... 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
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 '<<============ "quartz" wrote: 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...? "MissyLovesExcel" wrote: 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
Hi Missy,
See the adaptation in my response to your preceding post. --- Regards, Norman "MissyLovesExcel" wrote in message ... 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 '<<============ "quartz" wrote: 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...? "MissyLovesExcel" wrote: 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
Hello again,
Yes, I think you only need to modify one line. Change: .EntireRow.Insert TO: .EntireRow.Resize(2).Insert Shift:=xlDown HTH/ "MissyLovesExcel" wrote: 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 '<<============ "quartz" wrote: 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...? "MissyLovesExcel" wrote: 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
Sorry Norman, I hope I didn't misguide the OP.
"Norman Jones" wrote: Hi Missy, See the adaptation in my response to your preceding post. --- Regards, Norman "MissyLovesExcel" wrote in message ... 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 '<<============ "quartz" wrote: 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...? "MissyLovesExcel" wrote: 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enter blank row after subtotal "Total"
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! --- Regards, Norman "quartz" wrote in message ... Sorry Norman, I hope I didn't misguide the OP. "Norman Jones" wrote: Hi Missy, See the adaptation in my response to your preceding post. --- Regards, Norman "MissyLovesExcel" wrote in message ... 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 '<<============ "quartz" wrote: 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...? "MissyLovesExcel" wrote: 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 "quartz" wrote: 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/ "MissyLovesExcel" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtotal "grand total" populates at top of data | Excel Discussion (Misc queries) | |||
| How do I enter a blank, not "", into a cell with a conditional for | Excel Worksheet Functions | |||
| Linking two "total" pages to create a "Complete Total" page | Excel Worksheet Functions | |||
| excel formula to enter "0" if cell blank | Excel Worksheet Functions | |||
| Removing the word "Total" from subtotal cells | Excel Programming | |||
Linear Mode
