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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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.
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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.
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,328
Messages
5,723,730
Members
422,512
Latest member
MHau5

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
Top