Subtotal in macro does not know labels are in first row

[General Ledger]

Dear All,

When I run this code,

Rows("1:1").NumberFormat = "@"
 
Cells.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18, 19, 20), _
 Replace:=True, PageBreaks:= False, SummaryBelowData:=True

I get an error:

Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command.

I select OK for the option:

If you want the first row of the selection or list used as labels and not as data, click OK

The result is exactly as I want. Of course I would rather the user not have to tell Excel the first row contains the lables.

Here are cells A1:C2 to illustrate I have text in the first row:

<TABLE style="WIDTH: 294pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=392><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7131" width=195><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 146pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=195>Customer Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=96>Invoice</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=101>Advance/Invoice</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>ABC COMPANY INC</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">1004120866</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Invoice</TD></TR></TBODY></TABLE>

When I use the macro recorder and execute the Subtotal feature on the same data, it does not ask me for the row of my labels.

According to the VBA Help, the syntax does not ask for the row of the labels.

[B][I]expression[/I].Subtotal([I]GroupBy[/I], [I]Function[/I], [I]TotalList[/I], [I]Replace[/I],  _[/B]
[B][I]PageBreaks[/I], [I]SummaryBelowData[/I])[/B]

Why doesn't Excel know my first row has labels?

How can I tell the Subtotal function the labels are in the first row, or have the macro respond OK to the error?

Any ideas?

Thanks,

GL

 

[VoG]

See if this works

Application.DisplayAlerts = False
Cells.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18, 19, 20), _
 Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Application.DisplayAlerts = True

 

[General Ledger]

VoG,

Once again you have the solution.

So I might learn, what is the logic and purpose behind the code:

Application.DisplayAlerts = False

Is this a line of code that most every macro should include at the beginning?

Thanks,

GL

 

[VoG]

Is this a line of code that most every macro should include at the beginning?

No, definitely not! Only use it where you want to suppress an anticipated message. It is commonly used before deleting sheets for example to avoid the standard Excel warning.