Insert a row

  • Thread starter Thread starter Rex Dunlap
  • Start date Start date
R

Rex Dunlap

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
 
Doesn't that add a row before the total?
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
 
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?
Click to expand...
"Ron de Bruin" <[email protected]> wrote in
Click to expand...
message news:%[email protected]...
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)




"Rex Dunlap" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
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.
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?
Click to expand...
"Ron de Bruin" <[email protected]> wrote in
Click to expand...
message news:%[email protected]...
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)




"Rex Dunlap" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
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
 
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)




"Rex" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
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
 
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" &
Click to expand...
Rows.Count), "*Total*") = 1 Then Exit Sub
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Rex" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top