Find Total and insert one blank row below

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

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--
 
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
 
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)
 
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, 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()....
 
Back
Top