Originally Posted by
Norie
yasar
Try this.
[vba]
Application.DisplayAlerts = False
' your code
Application.DisplayAlerts = True[/vba]
By the way you really don't need all that selecting and you should avoid using Selection and On Error.
What is the purpose of the code?
The purpose of the code is to subtotal groups of ranges listed beneath each other. what I am doing with the code is:[vba]
On Error Goto yasar
For n = 3 To 7
Sheets(n).Select
Range("b1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
'until here Code is going to the right down corner of my first range
ActiveCell.Offset(-1, 0).Select
'the bottom corner of the first range is grand total bc I already have used the subtotal function once on this data, so with this I avoid the grand total at the bottom
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
'I select the range I need the subtotal of
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Do
SendKeys "~", True
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
'Until here I am trying to go to the bottom right corner of each range I have and offfset one row up
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
'there are some ranges only with one item so I do not need to select up
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
'with this code here I am trying to select one row addition on top of my 'range so the subtotals will not use my first row of data as heading
'this is the point where I get the error
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Else
SendKeys "~", True
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End If
Loop
'The code loops all the ranges I might have
Next
'code goes to the next sheet
yasar:
Resume Next
End Sub
[/vba]
I ended up using the code you gave it works fine I had to write a loop until function to stop the error message I get at the end of the sheet when it could not find a range to subtotal,
Thanks for the Help
So, what do you advise me to use instead of "Selects"
Yasar