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?

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?

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?

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?

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

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" wrote:

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?

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.
--
Gary''s Student - gsnu200828


"Chappy" wrote:

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" wrote:

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?