yasarayhanka
09-27-2007, 10:57 AM
Hello,
I am trying to do subtotals to ranges without head line
bc of this it is giving this warning message
"microsoft excel can not determine which row in your list or selection contains column labels , which are required for this command"
and I have to hit enter everytime this comes up so my code will keep working
this is the code I am using and I tried putting the sendkey but it does not work
I do not want to turn off the warning messages bc, that is how my code knows to go to the next sheet.
On Error GoTo yasar
For n = 3 To 7
Sheets(n).Select
Range("b1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
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
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
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
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
Next
yasar:
Resume Next
End Sub
thanks in advance for the help,
Yasar
I am trying to do subtotals to ranges without head line
bc of this it is giving this warning message
"microsoft excel can not determine which row in your list or selection contains column labels , which are required for this command"
and I have to hit enter everytime this comes up so my code will keep working
this is the code I am using and I tried putting the sendkey but it does not work
I do not want to turn off the warning messages bc, that is how my code knows to go to the next sheet.
On Error GoTo yasar
For n = 3 To 7
Sheets(n).Select
Range("b1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
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
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
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
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
Next
yasar:
Resume Next
End Sub
thanks in advance for the help,
Yasar