• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create excel table with sub-headings?

mdavid

Member
Hi, Sorry if this is a bit of a dummy question - but what's a forum for?
Never done this before so need some help.
I've attached a table, it's sorted by the columns A - C, what I want to do is have a row with the 3 fields A - C as a subheading, and all rows that have the same details in column A - B
grouped below the subheading. So subheading would be:

Species Name: Achillea fragrantissima Forssk. Memp ID: 15 Berc ID: 627

followed by columns D - L, row 1 contains the headings for these columns.

Thanks for any help
David
 

Attachments

Hui

Excel Ninja
Staff member
Select the Whole data range
Goto the Data, Sub Total menu and select SubTotal

upload_2019-3-17_11-57-0.png


Now got the first Sub Total Row, Row 5:
Type: ="Species Name: "&C4&" Memp ID: "&B4& " Berc ID: "&A4
Delete C5

Repeat this for all the Sub Total Rows

upload_2019-3-17_12-1-58.png
 

mdavid

Member
Hi Hui,
This is great - been tearing my hair out trying to do it with vba.
If I add the sub-headings for cols A - C - as you've done , then I'd like to remove those columns in the display and only show columns D - L, would that be possible?
Thanks for your help, really appreciated.
David
 

mdavid

Member
Hi,
Was about to implement this when I realized this is all manual.
I have over 100 species so I'm looking for something like this, but automated so every time the value in column A changes I want to insert this subheading at the top of the data.
Thanks for any help
David
 

mdavid

Member
After following Hui Ali's instructions I ended up with the resulting text appearing in D4 and not filling the row like Hui's example - what did I do wrong?subtotal3.JPG subtotal3.JPG sub-total2.JPG
 

mdavid

Member
Hi, This all seems a little complicated, all I want to do is add a sub heading (like the one above) - NOT a subtotal - as soon as a value changes.
Is there a solution more suitable for creating sub-headings.

Thanks
David
 

mdavid

Member
Hi Hui,
Really like to know how your solution produces a blank row without columns and with the text between specified groups, still trying to reproduce that behavior without success
Thanks
David
 

Hui

Excel Ninja
Staff member
I adjust it manually

If you want a VBA Solution I'll get to it on the weekend if nobody else does in between
 

mdavid

Member
Hi Hui,
Thanks for getting back to me, I'd really appreciate a VBA solution, I've been trying but haven't succeeded yet.
As I say I have over 100 species, so any solution that would produce what you did but without having to adjust it manually would be fine.

Thanks for your time
David
 

mdavid

Member
Hi, This is what I have so far:

Code:
Sub InsertHdrRows()
Dim lastrow As Long, i As Long
Application.ScreenUpdating = False
lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For i = lastrow To 2 Step -1
        If Cells(i, "A") <> Cells(i - 1, "A") Then
            Rows(i).Insert xlShiftDown
            Cells(i - 1, "E").Value = "species: " & Cells(i + 1, "C") & " berc ID: " & Cells(i + 1, "A") & " memp ID: " & Cells(i + 1, "B")
            ActiveCell.WrapText = True
            ActiveCell.EntireRow.AutoFit
        End If
    Next i

Application.ScreenUpdating = True
End Sub
Problem with this code is
1. it produces an additional blank row above and below the inserted heading.
ie. 3 rows rather than 1 are inserted.
2. The heading is not formatted, need to change word wrap so that text appears as a single line. Don't know how to do that in VBA.
 

mdavid

Member
Hi, In the end I went back to using the subtotal routine to insert header rows,
(no idea why I was getting 3 blank rows each time with my vba code).
Then the following code to do the rest of the formatting:
Code:
Sub InsertHdrRows()
Dim lastrow As Long, i As Long
Application.ScreenUpdating = False
lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lastrow
        If IsEmpty(Cells(i, "B")) Then 
        Cells(i, "E").Value = "Species: " & Cells(i + 1, "C") & "    Berc ID: " & Cells(i + 1, "A") & "  Memp ID: " & Cells(i + 1, "B")
        ActiveSheet.Range("A" & i & ":N" & i).Select
          With Selection
            .Font.Size = 16
            .Borders.LineStyle = xlNone
            .Interior.ColorIndex = 36
            .WrapText = False
          End With
        End If
    Next i

Application.ScreenUpdating = True
End Sub
Took me a while, but in the end I did the magic myself.
Thanks for giving me the opportunity
David
 

mdavid

Member
Hi Hui, Thanks for this, managed to solve it myself.
In your solution not seeing sub-headings when I click "Add Sub Totals".
"Clear Sub Totals" gives:
Run time error 438
Object doesn't support this property or method.

This is the line causing the problem
Code:
Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
    "A2:A" & lra), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
Also - not your problem - when I open the worksheet I get message:
"We can't update some of the links in your workbook"

Don't know what it's referring to, don't want any links, how do I get rid of them and this message?

Thanks for your help
David
 

Hui

Excel Ninja
Staff member
Those links were in your original file when I opened it !

What version of Excel are you using ?
 

mdavid

Member
Yeh, I know, it's somewhere in the worksheet - but I'm not using any links.
I'm using Excel 2016.

Thanks
David
 

Hui

Excel Ninja
Staff member
The links are in two Named Formula

The macro works fine for me in your file ?

upload_2019-3-23_23-16-38.png
 

Hui

Excel Ninja
Staff member
Did you copy the code to another file ?

Is the worksheet called Sheet1?
 

mdavid

Member
Hi Hui,
Really sorry, it's not working for me. I downloaded
Medicinal-Complaints-by-species(IH Edit).xlsm - including the code - opened it with Excel 2016.
Running Add_Sub_Totals() in debug mode - F8, I see it goes to Clear_Sub_Totals and stops on the same error I receive when I clicked "Clear Sub Totals", I don't receive an error message 'cause "On Error GoTo Finish" is triggered.

Thanks
David
 

Hui

Excel Ninja
Staff member
Are you on a Mac ?

Because it works for me when I download it from here?
 
Last edited:

Hui

Excel Ninja
Staff member
Also the last row of the Clear_Sub_Totals macro should be

Range("A" & lrb + 1 & ":L" & lra + 1).ClearContents
 

mdavid

Member
Hi Hui,
Thanks, this works.
There was a misunderstanding, I was looking for sub-headings at the start of a group not subtotals at the end.
To create what I wanted I used the Subtotal command and unchecked "Summery below data" box, then added the details (from the row below) and format I wanted to the lines added by the Subtotal command
Thanks for your help
David
 

alex.stone

New Member
Hello! I'm very interested in seeing how the subheadings can be added for filter purposes. The example I'd like to achieve is what has been presented in the attached image from earlier in this thread.
I'm less interested in subtotals but can include them if necessary. Could anyone suggest steps to incorporate subheadings such as these? Or were these added manually and the VBA was entered for intelligence within those manual "sections"?
Thank you!
 

Attachments

Top