View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student Gary''s Student is offline
external usenet poster
 
Posts: 11,058
Default how can I insert a blank row after each subtotal in a workshee

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

--
Gary''s Student - gsnu200828


"chappy" wrote:

It does contain the word subtotal in column A.
Chappy

"Gary''s Student" wrote:

How can you identify a subtotal row? Does it contain a SUBTOTAL() function in
some column?
--
Gary''s Student - gsnu200828


"chappy" wrote:

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?