Insert Rows after word total
 

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

Insert Rows after word total
 

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

Insert Rows after word total
 

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

"Peter T" wrote:

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

Insert Rows after word total
 

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" wrote:

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

Insert Rows after word total
 

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

"Elaine" wrote in message
...
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" wrote:

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

Insert Rows after word total
 

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

==========
"Elaine" wrote:

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" wrote:

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