Find Total and insert one blank row below
 

Normally I may have 140 to 180 items in a list, Im subtotaling and then
trying to insert a blank row under the *Total* in Column E. This code worked
fine the first couple time I ran it, but then stopped working. I cant
figure out why. It shouldnt be because of the 250 rows which now contain
some blanks (sometimes I will have more than 180 items; I want to capture
all). I know you can insert rows into a Subtotal list, so that's not it. At
one point I had a few of these guys in Column E:
#VALUE!
#VALUE! Total

I got rid of those, not that they should matter with the *, re-ran the code,
and still got the error. I cant figure out what is causing the error.

Code fails on this line:
If (Cells(i, 5).Value) Like "*Total*" Then

This is the whole sub:
Dim i As Long
For i = 250 To 1 Step -1
If (Cells(i, 5).Value) Like "*Total*" Then
Rows(i + 1).Insert
End If
Next i



Oh, also, how can I insert a bold line under each Total, from Column A to
Column T? Ive done this before. Now I cant find the code (that does this)
in my library.

Any thoughts?


Thanks so much!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Find Total and insert one blank row below
 

Hi Ryan

I have modified the code a bit.Give it a try..Alternatively you can use
Range.Find which would be faster (especially when you have more number of
rows)

Sub Macro()

Dim i As Long
For i = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If InStr(1, Cells(i, 5).Text, "Total", vbTextCompare) < 0 Then
Rows(i + 1).Insert
With Range("A" & i + 1).Resize(1, 20).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next i

End Sub

--
Jacob


"ryguy7272" wrote:

Normally I may have 140 to 180 items in a list, Im subtotaling and then
trying to insert a blank row under the *Total* in Column E. This code worked
fine the first couple time I ran it, but then stopped working. I cant
figure out why. It shouldnt be because of the 250 rows which now contain
some blanks (sometimes I will have more than 180 items; I want to capture
all). I know you can insert rows into a Subtotal list, so that's not it. At
one point I had a few of these guys in Column E:
#VALUE!
#VALUE! Total

I got rid of those, not that they should matter with the *, re-ran the code,
and still got the error. I cant figure out what is causing the error.

Code fails on this line:
If (Cells(i, 5).Value) Like "*Total*" Then

This is the whole sub:
Dim i As Long
For i = 250 To 1 Step -1
If (Cells(i, 5).Value) Like "*Total*" Then
Rows(i + 1).Insert
End If
Next i



Oh, also, how can I insert a bold line under each Total, from Column A to
Column T? Ive done this before. Now I cant find the code (that does this)
in my library.

Any thoughts?


Thanks so much!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Find Total and insert one blank row below
 

If you are looking for borders for the row with Total then change the Resize
range as below..

With Range("A" & i).Resize(1, 20).Borders(xlEdgeBottom)

--
Jacob


"Jacob Skaria" wrote:

Hi Ryan

I have modified the code a bit.Give it a try..Alternatively you can use
Range.Find which would be faster (especially when you have more number of
rows)

Sub Macro()

Dim i As Long
For i = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If InStr(1, Cells(i, 5).Text, "Total", vbTextCompare) < 0 Then
Rows(i + 1).Insert
With Range("A" & i + 1).Resize(1, 20).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next i

End Sub

--
Jacob


"ryguy7272" wrote:

Normally I may have 140 to 180 items in a list, Im subtotaling and then
trying to insert a blank row under the *Total* in Column E. This code worked
fine the first couple time I ran it, but then stopped working. I cant
figure out why. It shouldnt be because of the 250 rows which now contain
some blanks (sometimes I will have more than 180 items; I want to capture
all). I know you can insert rows into a Subtotal list, so that's not it. At
one point I had a few of these guys in Column E:
#VALUE!
#VALUE! Total

I got rid of those, not that they should matter with the *, re-ran the code,
and still got the error. I cant figure out what is causing the error.

Code fails on this line:
If (Cells(i, 5).Value) Like "*Total*" Then

This is the whole sub:
Dim i As Long
For i = 250 To 1 Step -1
If (Cells(i, 5).Value) Like "*Total*" Then
Rows(i + 1).Insert
End If
Next i



Oh, also, how can I insert a bold line under each Total, from Column A to
Column T? Ive done this before. Now I cant find the code (that does this)
in my library.

Any thoughts?


Thanks so much!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Find Total and insert one blank row below
 

Speechless, as usual. Whatever you're making, it should be more. I've seen
the InStr() used before, but I don't think I've really used it myself. What
was preventing my code from working?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

If you are looking for borders for the row with Total then change the Resize
range as below..

With Range("A" & i).Resize(1, 20).Borders(xlEdgeBottom)

--
Jacob


"Jacob Skaria" wrote:

Hi Ryan

I have modified the code a bit.Give it a try..Alternatively you can use
Range.Find which would be faster (especially when you have more number of
rows)

Sub Macro()

Dim i As Long
For i = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If InStr(1, Cells(i, 5).Text, "Total", vbTextCompare) < 0 Then
Rows(i + 1).Insert
With Range("A" & i + 1).Resize(1, 20).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next i

End Sub

--
Jacob


"ryguy7272" wrote:

Normally I may have 140 to 180 items in a list, Im subtotaling and then
trying to insert a blank row under the *Total* in Column E. This code worked
fine the first couple time I ran it, but then stopped working. I cant
figure out why. It shouldnt be because of the 250 rows which now contain
some blanks (sometimes I will have more than 180 items; I want to capture
all). I know you can insert rows into a Subtotal list, so that's not it. At
one point I had a few of these guys in Column E:
#VALUE!
#VALUE! Total

I got rid of those, not that they should matter with the *, re-ran the code,
and still got the error. I cant figure out what is causing the error.

Code fails on this line:
If (Cells(i, 5).Value) Like "*Total*" Then

This is the whole sub:
Dim i As Long
For i = 250 To 1 Step -1
If (Cells(i, 5).Value) Like "*Total*" Then
Rows(i + 1).Insert
End If
Next i



Oh, also, how can I insert a bold line under each Total, from Column A to
Column T? Ive done this before. Now I cant find the code (that does this)
in my library.

Any thoughts?


Thanks so much!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Find Total and insert one blank row below
 

Ryan, when we use Like statement it should be exactly same as what is
specified 'Total' not as total or 'TOTAL'..Hence we have used vbTextCompare
for Instr()....

--
Jacob


"ryguy7272" wrote:

Speechless, as usual. Whatever you're making, it should be more. I've seen
the InStr() used before, but I don't think I've really used it myself. What
was preventing my code from working?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

If you are looking for borders for the row with Total then change the Resize
range as below..

With Range("A" & i).Resize(1, 20).Borders(xlEdgeBottom)

--
Jacob


"Jacob Skaria" wrote:

Hi Ryan

I have modified the code a bit.Give it a try..Alternatively you can use
Range.Find which would be faster (especially when you have more number of
rows)

Sub Macro()

Dim i As Long
For i = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If InStr(1, Cells(i, 5).Text, "Total", vbTextCompare) < 0 Then
Rows(i + 1).Insert
With Range("A" & i + 1).Resize(1, 20).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next i

End Sub

--
Jacob


"ryguy7272" wrote:

Normally I may have 140 to 180 items in a list, Im subtotaling and then
trying to insert a blank row under the *Total* in Column E. This code worked
fine the first couple time I ran it, but then stopped working. I cant
figure out why. It shouldnt be because of the 250 rows which now contain
some blanks (sometimes I will have more than 180 items; I want to capture
all). I know you can insert rows into a Subtotal list, so that's not it. At
one point I had a few of these guys in Column E:
#VALUE!
#VALUE! Total

I got rid of those, not that they should matter with the *, re-ran the code,
and still got the error. I cant figure out what is causing the error.

Code fails on this line:
If (Cells(i, 5).Value) Like "*Total*" Then

This is the whole sub:
Dim i As Long
For i = 250 To 1 Step -1
If (Cells(i, 5).Value) Like "*Total*" Then
Rows(i + 1).Insert
End If
Next i



Oh, also, how can I insert a bold line under each Total, from Column A to
Column T? Ive done this before. Now I cant find the code (that does this)
in my library.

Any thoughts?


Thanks so much!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.