Insert Rows after word total

[Elaine]

I would like to insert two rows after the word Total appears in Column A
except at the bottom of the working range as I don't want to insert lines
between Total and Grand Total.

Dim RngCell as Range
Dim rCell as Long
If InStr(1, LCase(RngCell.Value), "total") > 0 then
Rows(rCell + 1 & ":" & rCell + 2).Insert

How do I tell the above code not to insert two rows if Grand Total (with a
space) appears in the row below total?

Thanks for your help.

--Elaine

 

[Peter T]

Hi Elaine,

Dim RngCell As Range
Dim rCell As Long
'code
If InStr(1, LCase(RngCell.Value), "total") > 0 Then
If InStr(1, LCase(RngCell.Value), "grand total") = 0 Then
Rows(rCell + 1 & ":" & rCell + 2).Insert
End If
End If

could also AND the two conditions

Regards,
Peter T

 

[Elaine]

Peter can you please check to see if the following is correct. When I run
this macro the line that says If InStr(1, LCase(RngCell.Value), "total") > 0
Then is highlighted. Thnaks for your help.

Dim RngCell As Range
Dim rCell As Long
'code
For rCell = 2000 To 1 Step -1
'problem with line below
If InStr(1, LCase(RngCell.Value), "total") > 0 Then
If InStr(1, LCase(RngCell.Value), "grand total") = 0 Then
Rows(rCell + 1 & ":" & rCell + 2).Insert
End If
End If

Next rCell

 

[Elaine]

Peter, one other thing. I don't see an offset or some other command so that
if Total is on one line and Grand Total is in the next, then lines will not
be inserted.

Thanks.

 

[Peter T]

Sorry Elaine, I didn't fully absorb your origninal question, have a go with
this -

Sub Test()
Dim rng As Range
Dim rw As Long
Dim rw0 As Long

Dim sAddr As String

Set rng = Range("a7:A2001")

rw0 = rng.Rows(1).Row - 1 ' in case first cell not in row 1

For rw = rng.Rows.Count To rng.Rows(1).Row Step -1
If InStr(1, LCase(rng(rw, 1)), "total") Then
If InStr(LCase(rng(rw + 1)), "grand total") = 0 Then
sAddr = CStr(rw + rw0) + 1 & ":" & CStr(rw + rw0 + 2)
Rows(sAddr).Insert
End If
End If
Next

End Sub

Sub sample()
Dim i&
Columns("A").Clear
For i = 1 To 7
Cells(i, 1) = i
Next
Range("A8") = "Total"

Range("A1:A8").AutoFill Range("A1:A2000")

Range("A2001") = "Grand Total"
End Sub

Regards,
Peter T

 

[RK]

Try this:

Dim RngCell As Range
Dim lngCounter As Long

For lngCounter = 1337 To 1 Step -1
Set RngCell = ActiveSheet.Cells(lngCounter, 1) '1 = Col A

'If Total appears in current cell
'and grand total is NOT in the next row
'and current cell is not grand total

If InStr(1, LCase(RngCell.Value), "total") > 0 And _
(InStr(1, LCase(RngCell.Offset(1, 0).Value), "grand total") = 0) And _
InStr(1, LCase(RngCell.Value), "grand total") <> 1 Then

RngCell.Offset(1, 0).Resize(2).EntireRow.Insert
End If
Next lngCounter

==========