Apply subtotal when I have a different range

**Ashrak** · 01-25-2013, 04:51 AM

Hello,
I tried to found a solution for my problem, but no succes through other threads.
I have a row with data, sometimes i have 1000 lines in row, other time I have 100 lines.
Because i don't want to lose data, I always apply automatic subtotal, I select range(A:B), and sum function. Selecting range A:A somethimes Excel crashes.
How can i determine the range? Let's say if I have 50 entries then the range is "Range(A1;B50)" this range must be inserted in subtotal function.

I hope I explained well my problem.
Thanks in advance.

**dilipandey** · 01-25-2013, 05:07 AM

HI Ashrak,

welcome to the forum.
Suggest you to create a defined name powered with offset / counta functions... post a sample workbook in case you stuck anywhere. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Ashrak** · 01-25-2013, 07:49 AM

Thanks, for reply.
My subtotal function looks like this:

Columns("A:B").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=2

I have 2 rows A and B, and applyed subtotal on them.
In VBA:
I can't manage how how to replace, Columns("A:B").Select, with my range for example A1:B183.

**jaslake** · 01-25-2013, 10:16 AM

HI Ashrak

Please wrap your Code in Code Tags. To do so go to Post #3, Edit Post, Highlight your Code, Click the # symbol, Click Save Changes.

Try this (not tested)

Option Explicit

Sub test()
    Dim LR As Long
    With ActiveSheet
        LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
        .Range("A1:B" & LR).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        .Outline.ShowLevels RowLevels:=2
        .Outline.ShowLevels RowLevels:=2
    End With
End Sub