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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try

Application.DisplayAlerts = False

immediately before the line that generates the query. Then set it to True afterwards.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,126
Messages
6,129,016
Members
449,480
Latest member
yesitisasport

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
Back
Top