Excel VBA Clear Subtotal Buttons

I have a Excel Workbook with multiple sheets that have subtotals. I would like to remove the subtotal buttons with VBA on all sheets.
shieldscoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
shieldscoConnect With a Mentor Author Commented:
I figured it out -- here is the code:
'Clear Subtotal Buttons
  Application.ScreenUpdating = False
    Dim wkSt As String
    Dim wkBk As Worksheet
    wkSt = ActiveSheet.Name
    For Each wkBk In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkBk.Activate
        Selection.ClearOutline
               
               
    Next wkBk
    Sheets(wkSt).Select
    Application.ScreenUpdating = True
0
 
regmigrantCommented:
Do you mean the subtotal fomula (eg: =subtotal(9,A1:a5) or that there is a Row/Column labelled 'subtotal' or that there is an actual button marked 'subtotal' and you want to delete it ?
0
 
shieldscoAuthor Commented:
No the subtotal buttons on the left. Manually I can go to Data. Ungroup and clea outline
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
regmigrantCommented:
assuming the data starts in A1 - try:


    Range("A1").Select
    Selection.RemoveSubtotal
0
 
shieldscoAuthor Commented:
I do not want to remove sutotals --I want to clear the outline.. clearoutline on all sheets in the workbook
0
 
shieldscoAuthor Commented:
'Clear Subtotal Buttons
  Application.ScreenUpdating = False
    Dim wkSt As String
    Dim wkBk As Worksheet
    wkSt = ActiveSheet.Name
    For Each wkBk In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkBk.Activate
        Selection.ClearOutline
               
               
    Next wkBk
    Sheets(wkSt).Select
    Application.ScreenUpdating = True
0
 
regmigrantCommented:
that's not the question that was asked - but whatever
0
 
shieldscoAuthor Commented:
I was able to figure out the solution without help from an expert.
0
 
regmigrantCommented:
its gone beyond the points but if you are asking questions please try to be explicit about what you are trying to achieve:-

your question:
"I would like to remove the subtotal buttons with VBA on all sheets. "

the answer you came up with:-
"Selection.ClearOutline"

Apart from not explaining what you meant by 'Subtotal buttons' you seem to be unaware that Outlines and Subtotals are different things - so it was a waste of time for all concerned.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.