I have a macro to sort and subtotal a set of data. This data will
always be different amount of lines. I can set the macro to subtotal
only current range. If I try to include all lines so that I account
for when the lines of data increase, it will not allow the first line
to be included.
Here is my recorded macro:
ActiveSheet.Range("$A$20:$H$2745").AutoFilter Field:=5,
Criteria1:="0"
Range("A21:A2745").Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$20:$H$634").AutoFilter Field:=5
Range("A20:H634").Select
Range("A634").Activate
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add
Key:=Range _
("A21:A634"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.SUBTOTAL GroupBy:=1, Function:=xlSum,
TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
Any ideas?
Thanks for your help.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
