I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each time that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area varies
from spreadsheet to spreadsheet. No spreadsheet goes
beyond row 700.

Help!

Try this Rex

Sub test()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString, LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" & Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex Dunlap" wrote in message ...
I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each time that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area varies
from spreadsheet to spreadsheet. No spreadsheet goes
beyond row 700.

Help!

Doesn't that add a row before the total?
"Ron de Bruin" wrote in message ...
Try this Rex

Sub test()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString, LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" & Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex Dunlap" wrote in message ...
I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each time that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area varies
from spreadsheet to spreadsheet. No spreadsheet goes
beyond row 700.

Help!

Yes, this does insert a row on top not below the word
total. I tried inserting ActiveCell.offset(1,0).select but
that did not do anything than move the activecell 1 cell
down.

-----Original Message-----
Doesn't that add a row before the total?

"Ron de Bruin" wrote in
message ...
Try this Rex

Sub test()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString,
LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" &
Rows.Count) _
.Find(What:=FindString,
LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex Dunlap"
wrote in message news:1274b01c3f663$a3f1e4c0
...
I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each time
that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area
varies
from spreadsheet to spreadsheet. No spreadsheet goes
beyond row 700.

Help!

Can you put Set Rng = Range("J" & Rng.Row + 1 & ":J" &
Rng.Row + 1) before the insert (haven't tried, just guessing)

So the while loop looks like:

While Not (Rng Is Nothing)
Set Rng = Range("J" & Rng.Row + 1 & ":J" & Rng.Row + 1)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" & Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend

Again, haven't tried it, just guessing.
"Rex" wrote in message ...
Yes, this does insert a row on top not below the word
total. I tried inserting ActiveCell.offset(1,0).select but
that did not do anything than move the activecell 1 cell
down.

-----Original Message-----
Doesn't that add a row before the total?

"Ron de Bruin" wrote in
message ...
Try this Rex

Sub test()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString,
LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" &
Rows.Count) _
.Find(What:=FindString,
LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex Dunlap"
wrote in message news:1274b01c3f663$a3f1e4c0
...
I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each time
that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area
varies
from spreadsheet to spreadsheet. No spreadsheet goes
beyond row 700.

Help!

Hi Rex

Sorry for sending the wrong sub
I made two examples and send the wrong one

Try this one

Sub test2()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString, LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.Offset(1, 0).EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" & Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex" wrote in message ...
Yes, this does insert a row on top not below the word
total. I tried inserting ActiveCell.offset(1,0).select but
that did not do anything than move the activecell 1 cell
down.

-----Original Message-----
Doesn't that add a row before the total?

"Ron de Bruin" wrote in
message ...
Try this Rex

Sub test()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString,
LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" &
Rows.Count) _
.Find(What:=FindString,
LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex Dunlap"
wrote in message news:1274b01c3f663$a3f1e4c0
...
I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each time
that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area
varies
from spreadsheet to spreadsheet. No spreadsheet goes
beyond row 700.

Help!

Thank you very much Ron. It did work.

I hope I can bother you and the others one last time on
this affair. The last two rows also have the word Total to
them and I do not want to insert a line there. Is it easy
to amend your macro to do that?

The last total (the grand total) will always read Supply
Total. The second last one will vary.

This has been so useful that I would most gladly delete
the two extra lines in the bottom of the sheet. Thank you
for the macro!


-----Original Message-----
Hi Rex

Sorry for sending the wrong sub
I made two examples and send the wrong one

Try this one

Sub test2()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString,
LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.Offset(1, 0).EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" &
Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex" wrote in
message ...
Yes, this does insert a row on top not below the word
total. I tried inserting ActiveCell.offset(1,0).select
but
that did not do anything than move the activecell 1 cell
down.

-----Original Message-----
Doesn't that add a row before the total?

"Ron de Bruin" wrote in
message ...
Try this Rex

Sub test()
Dim Rng As Range
FindString = "Total"
Set Rng = Range("J:J").Find(What:=FindString,
LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("J" & Rng.Row + 1 & ":J" &
Rows.Count) _
.Find(What:=FindString,
LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rex Dunlap"
wrote in message news:1274b01c3f663$a3f1e4c0
...
I have an Excel 2002 spreadsheet with subtotals.

Can someone show me how to add a row after each
time
that
the word 'xxx Total' appears?

The word 'Total' appears in Col J and the data area
varies
from spreadsheet to spreadsheet. No spreadsheet
goes
beyond row 700.

Help!




.