Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002-7: Modifying Sub Total Function to allow insert blank r | Excel Worksheet Functions | |||
Insert Blank Row Below Sub-Total? | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Autoatically insert blank row above total when no more rows | Excel Programming | |||
Macro code to test for blank row and insert blank row if false | Excel Programming |