PDA

View Full Version : Solved: Insert a new row after finding text "total" in a cell



obriensj
07-21-2008, 06:38 AM
Hi,

I need a bit of code where i can insert a new row in a spreadsheet.
The scenario is that i have a page with subtotals on it, such as Monday Total, Tuesday Total and so on.

What i need is a bit of code to insert a new row below the total row (Monday Total, Tuesday Total and so on) when the row has the words "Total" in it. So the code knows to read the word Total and then insert a new line below it.
Is an IF statement needed here?

Any help at all appreciated.

Thanks

Steve

grichey
07-21-2008, 07:12 AM
Is total only showing up a max of 1 time per sheet?

obriensj
07-21-2008, 07:21 AM
Hi Gavin,

Nope the word "Total" appears numerous times, could be 1-500 times.
The cell will always end with the word "Total" but will always have another name preceeding it, like Monday Total or John Total or Michael Total.

Thanks

Steve

grichey
07-21-2008, 07:44 AM
can it be anywhere in the sheet or just say column A?

obriensj
07-21-2008, 07:46 AM
Hi,

It will always be in Cloumn A.

Thanks

Steve

f2e4
07-21-2008, 08:16 AM
Try this:


Dim x As Integer

x = Columns(1).Find(what:="total", After:=Cells(1, 1),LookIn:=xlFormulas,
_ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row

With Cells(x, 1)
If .Value = "total" Then
.EntireRow.Insert xlShiftDown
End If
End With


Not sure if this will find them all or just one - if it is just one, you will need to add a loop to find the next one

grichey
07-21-2008, 08:29 AM
This works though it could be made cleaner by removing selects. I keep screwing something up when doing that though...

Option Explicit
Sub InsertAfterTotal()
Dim lastCell As Integer
Dim counter As Integer

lastCell = Range("A65536").End(xlUp).Row
counter = lastCell
Do
Range("A" & counter).Select

If Right(Trim(Selection), 5) = "Total" Then
Rows(counter + 1).Insert Shift:=xlDown

End If



counter = counter - 1
Loop Until counter = 0




End Sub

grichey
07-21-2008, 08:32 AM
Ha. Without trying this, it looks like if you were to loop it, you'd easily throw it into an infinite loop. You'll need to set a start row or something to keep track of where your first find result was so it knows to quit or you'll go around and around inserting rows.

Try this:


Dim x As Integer

x = Columns(1).Find(what:="total", After:=Cells(1, 1),LookIn:=xlFormulas,
_ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row

With Cells(x, 1)
If .Value = "total" Then
.EntireRow.Insert xlShiftDown
End If
End With


Not sure if this will find them all or just one - if it is just one, you will need to add a loop to find the next one

obriensj
07-21-2008, 08:45 AM
Thanks Guys for the repllies.
Think i do need some sort of loop.
Was thinking of this adding something like this in, would this do it?:

Do Until IsEmpty(ActiveCell.Offset(0, 1))
ActiveCell.Offset(1, 0).Select
Loop

grichey
07-21-2008, 08:46 AM
if you use mine, it works as is.

grichey
07-21-2008, 08:48 AM
Thanks Guys for the repllies.
Think i do need some sort of loop.
Was thinking of this adding something like this in, would this do it?:

Do Until IsEmpty(ActiveCell.Offset(0, 1))
ActiveCell.Offset(1, 0).Select
Loop

ehh, the activecell business gets cumbersome. Doing it that way will also result in trouble for you if you have any blank rows or empty cells which unless your positive they'll never occur do tend to pop up.

f2e4
07-21-2008, 08:53 AM
Ha. Without trying this, it looks like if you were to loop it, you'd easily throw it into an infinite loop. You'll need to set a start row or something to keep track of where your first find result was so it knows to quit or you'll go around and around inserting rows.

Fine...try this then

Sub Find_total()
Dim Rng As Range, Foundit As String

With Sheet1.Range("A1:A100")
Set Rng = .Find(what:="total", After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not Rng Is Nothing Then
Foundit = Rng.Address
Do
Rng.EntireRow.Insert xlShiftDown
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> Foundit
End If
End With

Set Rng = Nothing

End Sub

obriensj
07-21-2008, 08:55 AM
Thanks Gavin for your help, worked a treat.

mdmackillop
07-21-2008, 10:15 AM
When looping to insert/delete rows, it's easier to start at the bottom. It avoids problems with repeated values in adjacent cells.
For large number of rows, I would use f2e4's Find solution.


Option Explicit
Sub InsertAfterTotal()
Dim i As Long

For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Right(Cells(i, 1), 5) = "Total" Then
Cells(i + 1, 1).EntireRow.Insert
End If
Next
End Sub

karldou
07-29-2008, 07:34 AM
Hi, sorry to thread hijack, but i am looking at doing a similar task, however it involves deleting some rows.

Firstly what i want the macro to do is to search the sheet, and when it finds the word "Termination" in Column B, it will delete all the rows above.
The word "Termination" can appear on different rows, depending on the size of the HTML import.

I tried to do this myself by amending Grichey's VBA script above, but when running, the debug catches an overflow error.



Option Explicit
Sub InsertAfterTotal()
Dim lastCell As Integer
Dim counter As Integer

lastCell = Range("A65536").End(xlDown).Row
counter = lastCell
Do
Range("B" & counter).Select

If Right(Trim(Selection), 5) = "Termination" Then
Rows(counter + 1).Insert Shift:=xlUp

End If



counter = counter - 1
Loop Until counter = 0




End Sub


Thanks in advance for your help :)

obriensj
07-29-2008, 09:05 AM
Hi,

Try this bit of code.
Not very developed by any means but it worked for me.

Steve




Option Explicit
Sub DeleteRowText()
Cells.Find(What:="Termination", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell = "Termination"
Selection.EntireRow.Delete
'ActiveCell.Offset(1, 0).Select
Loop

End Sub

mdmackillop
07-29-2008, 12:06 PM
Hi, sorry to thread hijack, but i am looking at doing a similar task, however it involves deleting some rows.

Firstly what i want the macro to do is to search the sheet, and when it finds the word "Termination" in Column B, it will delete all the rows above.
The word "Termination" can appear on different rows, depending on the size of the HTML import.



Avoid the loop to speed up execution

Sub DelRows()
Range(Cells(1, 2), Columns(2).Find(What:="Termination", _
After:=Cells(1, 2))).EntireRow.Delete
End Sub

karldou
07-30-2008, 01:20 AM
Thanks mdmac! that worked perfectly!

thanks
Karl