greyangel
08-22-2017, 09:08 AM
The code below sorts all the data by item and then applies a subtotal to the data and then takes it to the 2nd row level:
Sub datadump()
Application.ScreenUpdating = False
On Error Resume Next 'turn off error reporting
ActiveSheet.ShowAllData
On Error GoTo 0 'turn error reporting back on
Range("D2").CurrentRegion.Sort Key1:=Range("D2"), _
DataOption1:=xlSortTextAsNumbers, _
Header:=xlYes
lastvalue = "A" & Range("A2").End(xlDown).Offset(1).Row & ":" & "A" & Range("A2").End(xlDown).End(xlDown).Row
Range(lastvalue).EntireRow.Delete
ActiveSheet.UsedRange
resetingcolumns = ActiveSheet.UsedRange.Columns.Count
Range("A1").Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7, 9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
For some reason the "Activesheet.outline.showlevels rowlevels:=2" is being skipped. Could someone help me out and tell me why that line of code is being skipped. When I performed this exercise on smaller files the entire code works. If you would like the file that I am having an issue with I can send it to you to look at, however the file is 3.57 mb which is to big to post here.
Sub datadump()
Application.ScreenUpdating = False
On Error Resume Next 'turn off error reporting
ActiveSheet.ShowAllData
On Error GoTo 0 'turn error reporting back on
Range("D2").CurrentRegion.Sort Key1:=Range("D2"), _
DataOption1:=xlSortTextAsNumbers, _
Header:=xlYes
lastvalue = "A" & Range("A2").End(xlDown).Offset(1).Row & ":" & "A" & Range("A2").End(xlDown).End(xlDown).Row
Range(lastvalue).EntireRow.Delete
ActiveSheet.UsedRange
resetingcolumns = ActiveSheet.UsedRange.Columns.Count
Range("A1").Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7, 9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
For some reason the "Activesheet.outline.showlevels rowlevels:=2" is being skipped. Could someone help me out and tell me why that line of code is being skipped. When I performed this exercise on smaller files the entire code works. If you would like the file that I am having an issue with I can send it to you to look at, however the file is 3.57 mb which is to big to post here.