Select "OK" in sort warning box

Muleskin57

New Member
Joined
Dec 22, 2016
Messages
23
I'm running a large macro that reformats a data dump from an accounting system. It sorts several times and subtotals. During the running of the macro, I get an "error" message that Excel cannot determine the column header row. I have been hitting ENTER ("OK") and the macro moves on until the next instance of that command and I must hit ENTER or select "OK" for it to complete. How can I automate this macro completely, getting the offending stoppages to cease?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Difficult to say without seeing the code.
 
Upvote 0
I think it emanates from this sub-macro:

Code:
Sub Sort_Programs()
'
' Sort_Programs Macro
'


'
    Sheets("DATA DUMP").Select
    Cells.Select
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "i2:i3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "k2:k3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "l2:l3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP").Sort.SortFields.Add Key:=Range( _
        "j2:j3002"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("DATA DUMP").Sort
        .SetRange Range("A1:WWW3002")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Sheets("DATA DUMP (Exp)").Select
    Cells.Select
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "i2:i926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "k2:k926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "l2:l926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort.SortFields.Add Key:=Range( _
        "j2:j926"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("DATA DUMP (Exp)").Sort
        .SetRange Range("A1:AO926")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Sheets("DATA DUMP").Select
End Sub
 
Last edited by a moderator:
Upvote 0
I can see nothing in that code that would give the problemyou have described.
What exactly dose the error message say?
 
Upvote 0
"Microsoft Excel cannot determine which row in your list or selection 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.
-if you selected a subset of cells in error, select a single cell, and try the command again.
-To create column lables, click Cancel, and enter a text label at the top of each column of data.
-For information about creating labels that are easy to detect, click Help.

OK Cancel Help"


OK is the correct choice and is the default, so I manually hit Enter.
 
Last edited:
Upvote 0
I cannot replicate that error, with the code you've supplied.
Do you have any merged cells?
 
Upvote 0
Thank you. I crashed the macro and the debugger landed me in this sub-macro. It has the right number of Subtotal commands for the number of error boxes that appear.

Sub Subtotal_Prog()
'
' Subtotal_Prog Macro
'


'
Sheets("DATA DUMP").Select
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 14, _
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7, 14, _
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 14, _
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select
Sheets("DATA DUMP (Exp)").Select
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 14, _
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7, 14, _
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 14, _
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select
Sheets("DATA DUMP").Select
End Sub
 
Last edited:
Upvote 0
I've never used grouped rows/columns, so unfortunately cannot help any further.
Hopefully somebody else will step in.
 
Upvote 0
Having played around a bit, do you have a header row?
 
Upvote 0

Forum statistics

Threads
1,202,909
Messages
6,052,495
Members
444,587
Latest member
ezza59

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