Find Total and insert one blank row below

[ryguy7272]

Normally I may have 140 to 180 items in a list, I’m 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 can’t
figure out why. It shouldn’t 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 can’t 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? I’ve done this before. Now I can’t find the code (that does this)
in my library.

Any thoughts?


Thanks so much!
Ryan--

 

[Jacob Skaria]

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 Skaria]

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)

 

[ryguy7272]

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?

 

[Jacob Skaria]

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()....