Insert a line after each subtotal in excel 3

[daveonion]

Hi just wondering if anybody knows how to insert a line after each subtotal using code,

thanks in advance

Dave

 

[MakeItSo]

If you refer to a border line, you could use the following:

Range([YourSubtotalCell]).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

This will add a medium weighted underline to your subtotals cell.

Greetings,
Andy

 

[rss01]

I was looking for the same thing an came across this at microsoft. I think it could be easly modified to insert if it finds a subtotal

Sub ShowWeeks()
Dim iToday As Integer
Dim iYesterday As Integer
Range("A2&quot.Select
iYesterday = Weekday(ActiveCell.Value)
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
iToday = Weekday(ActiveCell.Value)
If iToday < iYesterday Then
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
iYesterday = iToday
Loop
End Sub

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexvba/html/odc_5709_chap05idx.asp

 

[daveonion]

hi, i thought i'd better explain,
i've got report which can contain 1-n columns, the macro works out how many rows there are and then inserts the subtotal in, however the way it inserts the subtotal it only leaves a blank row to seperate each group, i however need it to put in 3 blank rows to seperate each group,

any ideas

thanks

 

[rss01]

Can you post your code that inserts the subtotal.

 

[MakeItSo]

At the point, you insert the subtotal, add:
For [any counter] = 1 to 3
Selection.EntireRow.Insert
Next

Andy

 

[daveonion]

hi makeitso

the code is just the same you get when you run a macro,i.e

Range(&quot;H1&quot.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

 

[daveonion]

hi i found this code on the microsoft website

Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, &quot;h&quot.End(xlUp).Row To 1 Step -1
If Cells(i, &quot;h&quot.Value = &quot;subtotal&quot; Then Cells(i, &quot;h&quot.EntireRow.Insert
Next i

obviously .value = &quot;subtotal&quot; is not going to work, but any ideas on how i could refer to it?

 

[SkipVought]

Dave,

It's a mistake to insert blank lines into a table -- it DESTROYS the table!

Better to FORMAT the way it looks, which is the objective, isn't it?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[daveonion]

Hi SkipVought,

Nope the table is already formatted but a user has requested blank lines to be inserted after each subtotal

thanks

 

[SkipVought]

Did you ever ask why?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Just a passing thought, but if the user wants that merely for the sake of appearance (e.g. when printing or scrolling), how about simply adjusting the row height for either the subtotal line and/or the line following?

 

[daveonion]

sure did, its for ease of reading, so they can see automatically where a new subtotal is added (its a large spreadsheet

 

[daveonion]

that would be fine, but where would you put that in the code?

 

[rss01]

daveonion,

I've been following this tread because I need something similar to what your looking for. I showed this to a programmer that works with me and this is what she came up with. hopefully you can use it.

Public Sub InsertRow()

Dim intJ As Integer
Dim strJ

Application.ScreenUpdating = False

For intJ = 2 To 2000
For Each strJ In Array(&quot;I&quot
If Left(Range(strJ & intJ).Formula, 9) = &quot;=SUBTOTAL&quot; Then
Range(strJ & intJ + 1).Select
Selection.EntireRow.Insert
End If
Next strJ
Next intJ


End Sub

 

[SkipVought]

Well then for &quot;ease of reading&quot; a row is NOT required. Formatting can accomplish the requirements without destroying the table.

What kind of formatting???

Increase ROW HEIGHT on the break and vertical align top

and/or

Border on the break

What do you think?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[daveonion]

Increase row height would be good, and the border on the break too, yep that would be excellent

 

[Zathras]

Here is a code snippet that you can modify many different ways as Skip has suggested. (Set bold, color, font etc.)
[blue]

Sub test()
Dim c As Range
  For Each c In Intersect(Range("A:A"), ActiveSheet.UsedRange)
    If Right(c.Value, 6) = " Total" Then
      c.RowHeight = 16
      c.Offset(1, 0).RowHeight = 60
    End If
  Next c
End Sub

[/color]
You can do anything you want once you have your hands on c which is the cell in column &quot;A&quot; of a subtotal row.

 

[SkipVought]

Sub FormatRow()
  With ActiveSheet.UsedRange
     c1 = .Column
     c2 = c1 + .Columns.Count - 1
  End With
  For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
    With Cells(i, "a")
        If .Value Like "*Total*" Then
            .EntireRow.RowHeight = .EntireRow.RowHeight * 2
            With Range(Cells(.Row, c1), Cells(.Row, c2)).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThick
            End With
        End If
    End With
  Next i
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com