Insert a row
 

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!

Insert a row
 

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!

Insert a row
 

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!

Insert a row
 

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!

Insert a row
 

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!

Insert a row
 

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!

Insert a row
 

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!




.

Insert a row
 

Hi Rex

Maybe this?
I am sure there are better ways

Sub test3()
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)
If Application.WorksheetFunction.CountIf(Range("J" & _
Rng.Row + 1 & ":J" & Rows.Count), "*Total*") = 1 Then Exit Sub
Wend
End Sub


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



"Rex" wrote in message ...
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!




.

Insert a row
 

What a time saver!! I have tried it on about 4 sheets so
far and it works just great. Thank you very very much.

It used to take me about 10 minutes per sheet and now it
doesn't take that many seconds!

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

Maybe this?
I am sure there are better ways

Sub test3()
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)
If Application.WorksheetFunction.CountIf(Range
("J" & _
Rng.Row + 1 & ":J" &
Rows.Count), "*Total*") = 1 Then Exit Sub
Wend
End Sub


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



"Rex" wrote in
message ...
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 news:%
...
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!




.



.

Insert a row
 

Thanks for the feedback Rex
Always nice to know that it is working<g

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



"Rex" wrote in message ...
What a time saver!! I have tried it on about 4 sheets so
far and it works just great. Thank you very very much.

It used to take me about 10 minutes per sheet and now it
doesn't take that many seconds!

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

Maybe this?
I am sure there are better ways

Sub test3()
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)
If Application.WorksheetFunction.CountIf(Range
("J" & _
Rng.Row + 1 & ":J" &
Rows.Count), "*Total*") = 1 Then Exit Sub
Wend
End Sub


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



"Rex" wrote in
message ...
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 news:%
...
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!




.



.