Suppress Column Label Warning when Subtotaling

sashimigrade

New Member
Joined
Mar 16, 2006
Messages
4
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

Code:
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]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Application.DisplayAlerts = False

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

sashimigrade

New Member
Joined
Mar 16, 2006
Messages
4
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
 

Forum statistics

Threads
1,136,586
Messages
5,676,671
Members
419,642
Latest member
Hyakkivn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top