none
Selection.Subtotal in Excel VBA

    Question

  • When I try and run subtotal with VBA I keep getting the error box saying it cannot tell what row is the header even though I have pretty clear, bolded headers that don't look anything like a data line. Is there any way I can adjust the following code (which works fine) so it executes without error box popping up?

    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(2), _

            Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    Thanks.

    Wednesday, November 16, 2011 3:04 PM

Answers

  • I need to see more of your code to determine what th eproblem is.  I don't know what cells are selected and don't recommend using selection.  I can't tell if the selection is setting the correct sheet which may be the reason for the problem.  I you post of you code I will make the appropriate changes.
    jdweng
    Wednesday, November 16, 2011 4:25 PM
  • Excel will not look at the formatting to determine whether your data have headers. This will be determined based on the data you have provided.

    If you have a header for the categories but no header for the numeric values, then Excel will not be able to recognize what the header is.

    Now, the grouping will still work if you click on OK in the message box.

    If you do not want this pop-up to appear you can still make use of the DisplayAlerts property

    Application.DisplayAlerts = False
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Application.DisplayAlerts = True

    Be aware this might not be a good idea here. This message box may be important, since it informs you that Excel is not certain to have selected the correct range for your data. If your grouping does not give the expected result, you will have to remember that.

    If you want to make certain that Excel will not display this alert, then you will have to add a header to all of your columns. If you do not want anything to be displayed above your numeric values, then just use the following simple formula:

    =""

    Thursday, November 17, 2011 7:22 AM

All replies

  • I need to see more of your code to determine what th eproblem is.  I don't know what cells are selected and don't recommend using selection.  I can't tell if the selection is setting the correct sheet which may be the reason for the problem.  I you post of you code I will make the appropriate changes.
    jdweng
    Wednesday, November 16, 2011 4:25 PM
  • Excel will not look at the formatting to determine whether your data have headers. This will be determined based on the data you have provided.

    If you have a header for the categories but no header for the numeric values, then Excel will not be able to recognize what the header is.

    Now, the grouping will still work if you click on OK in the message box.

    If you do not want this pop-up to appear you can still make use of the DisplayAlerts property

    Application.DisplayAlerts = False
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Application.DisplayAlerts = True

    Be aware this might not be a good idea here. This message box may be important, since it informs you that Excel is not certain to have selected the correct range for your data. If your grouping does not give the expected result, you will have to remember that.

    If you want to make certain that Excel will not display this alert, then you will have to add a header to all of your columns. If you do not want anything to be displayed above your numeric values, then just use the following simple formula:

    =""

    Thursday, November 17, 2011 7:22 AM