HI ALL, I AM TRYING TO SORT THEN SUBTOTAL (ONLY IF THERE IS DATA)HOWEVER AM GETTING SOME ERRORS...IE IN FORMAT
I HAVE A ROW(ROW 1)WITH HEADINGS BUT I WANT TO SORT ASENDING IE IF DATA IS VISIBLE....THE CURRENT SORT PLACES HEADING ROW AT BOTTOM WHICH I DO NOT WANT , I WANT THIS TO STAY AS IS BUT SORT UNDERNEATH
THEN I WISH TO SUBTOTAL BY CHANGE IN COLUM H AND SUBTOTAL COLUMN J, HERE IS MY CODE BELOW , CAN ANYONE PLESE HELP
THX
Code
With ActiveSheet.UsedRange
If WorksheetFunction.CountA(Columns(13)) > 3 Then
SELECTION .Sort Key1:=Range("P1"), Order1:=xlDescending, Header:=
xlGuess , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(9, 10, 13) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
For Each rcell In Range("O1", Range("O" & Rows.Count).End(xlUp))
If rcell.Font.Bold = True Then rcell.Offset(0, -6).Font.Bold = True
If rcell.Font.Bold = True Then rcell.Offset(0, -6).Font.ColorIndex = 8
If rcell.Font.Bold = True Then rcell.Offset(0, -6).Font.Underline = True
If rcell.Font.Bold = True Then rcell.Offset(0, -6).Interior.ColorIndex = 5
If rcell.Font.Bold = True Then rcell.Offset(0, -7).Font.Bold = True
If rcell.Font.Bold = True Then rcell.Offset(0, -7).Font.ColorIndex = 8
If rcell.Font.Bold = True Then rcell.Offset(0, -7).Font.Underline = True
If rcell.Font.Bold = True Then rcell.Offset(0, -7).Interior.ColorIndex = 5
Next rcell
Else
MsgBox "NO DATA TO SUBTOTAL"
End If
End With
Display More