Suppress Column Label Warning when Subtotaling

[sashimigrade]

I’m creating a macro that autofilters and subtotals by pressing a button on the sheet itself for individuals who aren’t excel savvy. I seem to have it working except one thing. How do I get rid of the excel warning stating, “Microsoft Excel cannot determine which row in your list contains column labels, which are required for this command.”... "If you want the first row of the selection or list used as labels and not as data, click OK."...??? The one that gives you the warning and then 4 bullet points offering suggestions. I wish I could attach an image of the warning!

Is there a way to suppress this warning within my code?

I believe I’m receiving this warning when the code tries to execute the subtotal. I'd rather not have the end users click through a warning message before looking at their reports. Please excuse my code, I’m an amateur.

Thanks for your time.

-Mike

Private Sub PurchasedServices_Click()
  Dim c As Long
  Application.ScreenUpdating = False
  If ActiveSheet.AutoFilterMode Then
    Cells.Select
    Selection.RemoveSubtotal
    Selection.AutoFilter
    c = ActiveSheet.UsedRange.Rows.Count + 1
    Range("A5:Z" & c).Select
    Selection.AutoFilter Field:=1, Criteria1:="Purchased Services"
    c = ActiveSheet.UsedRange.Rows.Count + 1
    Range("A5:Z" & c).Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
        9, 10, 11, 12, 13, 14, 15, 16, 17, 19, 22, 23, 24, 25, 26), Replace:=True, PageBreaks:= _
        False, SummaryBelowData:=True
    Range("A4").Activate
  Else
    Cells.Select
    Selection.RemoveSubtotal
    c = ActiveSheet.UsedRange.Rows.Count + 1
    Range("A5:Z" & c).Select
    Selection.AutoFilter Field:=1, Criteria1:="Purchased Services"
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
        9, 10, 11, 12, 13, 14, 15, 16, 17, 19, 22, 23, 24, 25, 26), Replace:=True, PageBreaks:= _
        False, SummaryBelowData:=True
    Range("A4").Activate
  End If
  Application.ScreenUpdating = True
End Sub

[/img]

 

[VoG]

Try

Application.DisplayAlerts = False

immediately before the line that generates the query. Then set it to True afterwards.

 

[sashimigrade]

It worked! Many thanks to VoG II for the help. I spent about six months in Great Britain and loved it. God Save the Queen!

-Mike