Code for adding lines

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Hello to all.

This may be a stretch, but can a code be written to insert a line or two in between each subtotal that piggybacks one another on a worksheet?

Thanks,

Anthony
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Writing the code to insert blank lines in the easy part. The hard part is identifying where to insert the lines. If you can include sample data so we can see how the data is laid out, we can develop a plan of attack.
 
Upvote 0
Very well then.....here it goes.

The data range on the worksheet is A4:G1000 with the subtotal formulas in columns E, F & G. I have two different subtotals (one that subtotals each state in the Western Region and one that subtotals each region; they are located in columns C & D, do I need to provide you with the specific cells or can I adjust the code later?

Many Thanks,

Anthony
 
Upvote 0
If I could see some sample output, it would be quite helpful. I am on my way out the door, so I won't be able to look at it again until tomorrow morning.
 
Upvote 0
Region TCode SCode Units $
West CA 9305 10 $0
West CA 9311 10 $315
West ME 9311 10 $3,015
West ME 9311 10 $3,015
West NV 9311 10 $3,675
West NV 9311 10 $4,149
West Total 60 $14,169
East ME 9311 10 $4,625
East ME 9311 10 $1,110
East MD 9318 10 $6
East MD 9318 10 $406
East Total 40 $6,147
Grand Total 100 $20,316

This is an example of how a report might look. Once I complete the subtotal process, I would collapse parts of the worksheet....use the GoTo Special Function and apply varios formats. Next, with the report in full view, I would now select Formulas from GoTo Special, which would highlight those cells that contain formulas which would then allow me to apply lines from the format cells function. Now, with the NV subtotal directly over the NV subtotal, once I apply the lines using the GoTo Formula funtion, I have to manually reset the lines because they do not appear the way I'd like them to. This is why I was hoping I could insert rows before I use the GoTo formula function.

Does all this make sense?

Sorry for being so long winded.

Thanks,

Anthony
 
Upvote 0
Ok, try this code:

Sub InsertRows()
'
'Start at cell A1
Range("A1").Select
'Loop through finding the word Total and insert blank line afterwards
Do
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
'If you find Grand Total, stop you are done
If Left(ActiveCell.Value, 11) = "Grand Total" Then
Exit Sub
End If
Loop

End Sub
This message was edited by jmiskey on 2002-11-01 09:12
 
Upvote 0
Upon running the code, it returned the following error message:

Compile error: Named Argument Not Found---and it highlighted: SearchFormat:=

Any suggestions?

Thanks again!!!

Anthony
 
Upvote 0
Yes. I have seen that error many times. The Search Format argument is a new argument in Excel XP, which is what I am using. You must be using an older version of Excel.

The solution is quite simple. Just delete that argument from the search statement so that it looks like the following:

Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

This should work.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top