PDA

View Full Version : Solved: Selecting the one row above with the relative range



yasarayhanka
08-14-2007, 09:33 AM
I am trying to select a relative range and take the subtotals of it, my relative ranges do not have the top row as headlines so I need to start selecting from one row above the relative range ( at subtotal it accepts the top line of the range as headlines) and I want to loop this untill there are no relative ranges below my data.

here is the code I tried but it only selects two rows of my range.


StartCell = ActiveCell.Offset(0, 0).Address
EndCell = ActiveCell.Offset(0, 2).Address
Range(StartCell, EndCell).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True



I will appreciate any help you guys can provide.
Thanks in advance

Yasar

Bob Phillips
08-14-2007, 09:43 AM
Selection.Offest(-1,0).Resize(Selection.Rows.Count+1).Select


I think!

yasarayhanka
08-14-2007, 10:18 AM
Selection.Offest(-1,0).Resize(Selection.Rows.Count+1).Select


I think!

It still does the same thing
it stops at the first row of the range

Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Range(Selection, Selection.End(xlDown)).Select

this is the way I implemented it, Also my range is three coumns columns A,B,C

yasar

yasarayhanka
08-14-2007, 10:40 AM
I put the code in the wrong row,
ity was supposed to be like this:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(-1, 0).Resize(Selection.Rows.Count + 1).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Range(Selection, Selection.End(xlDown)).Select

thanks for the help XLD