how can I insert a blank row after each subtotal in a worksheet

[chappy]

I imported data, sorted and subtotalled the data by customer. Is there a way
I can insert a blank row after each subtotal, for readability when I print it
as a report?

 

[Gary''s Student]

How can you identify a subtotal row? Does it contain a SUBTOTAL() function in
some column?

 

[chappy]

It does contain the word subtotal in column A.
Chappy

 

[Gary''s Student]

Here is a small macro. It scans down column A, looking for the word "subtotal"

When it finds the word, it inserts a blank row just below:

Sub lineAdd()
n = Cells(Rows.Count, 1).End(xlUp).Row
Dim radd As Range
Set radd = Nothing
For i = 1 To n
v = Cells(i, 1).Value
If InStr(UCase(v), "SUBTOTAL") <> 0 Then
If radd Is Nothing Then
Set radd = Cells(i, 1)
Else
Set radd = Union(radd, Cells(i, 1))
End If
End If
Next
radd.Offset(1, 0).EntireRow.Insert
End Sub



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[chappy]

Thanks a ton. I am very interested in what macros can do and will be
pursuing their use. I would have never figured this out. Really appreciated
it.

Chappy

 

[Chappy]

Hi Gary's Student:
When I run the macro, it gives the following error and appears to be talking
about the next to the last line

Object variable or With block variable not set

Could use your help, again.

Chappy

 

[Gary''s Student]

It is failing. It can't find any rows in which the column A cell has the
text "Subtotal" or "subtotal" in them. For example if cell A10 has the text:

This is a subtotal

then a blank row would be inserted below. If subtotal appears as a function
rather than as text we will make a small change to the macro.


Let me know.

 

[Chappy]

HI Again
I looked at the cell properties and it is text. It is the result of running
the subtotal function on the data in the worksheet. The text actually is the
customer name followed by the word Total. I had changed your instructions
from the word Subtotal to Total.

Is the customer name messing it up? Also, I suppose if I knew more about
it, I could do the subtotalling with the VB instructions and insert the space
at that point?

Thanks again

Chappy

P.S. Where is there a good source for instruction on VB.

 

[Gary''s Student]

Here is a discrete example. In A1 thru C20:

Joe 49 22
Joe 28 20
Joe 75 14
Joe total 152 56
jim 46 70
jim 53 63
jim 93 12
jim 58 17
jim total 250 162
alan 42 69
alan 31 23
alan 83 43
alan 86 34
alan total 242 169
james 21 76
james 42 42
james 97 56
james 85 49
james 75 87
jame total 320 310

The new macro is:


Sub lineAdd()
n = Cells(Rows.Count, 1).End(xlUp).Row
Dim radd As Range
Set radd = Nothing
For i = 1 To n
v = Cells(i, 1).Value
If InStr(UCase(v), "TOTAL") <> 0 Then
If radd Is Nothing Then
Set radd = Cells(i, 1)
Else
Set radd = Union(radd, Cells(i, 1))
End If
End If
Next
If radd Is Nothing Then
MsgBox ("I failed to find anything")
Else
radd.Offset(1, 0).EntireRow.Insert
End If
End Sub

After running this macro we see:

Joe 49 22
Joe 28 20
Joe 75 14
Joe total 152 56

jim 46 70
jim 53 63
jim 93 12
jim 58 17
jim total 250 162

alan 42 69
alan 31 23
alan 83 43
alan 86 34
alan total 242 169

james 21 76
james 42 42
james 97 56
james 85 49
james 75 87
jame total 320 310

 

[chappy]

worked like a charm. Thanks again. And, sorry about the P.S., forgot you
had already given that reference in your first response.

Chappy