Help with using the SUBTOTAL formula in a macro.

[SMonczka]

I have written a macro that filters a range of data, then subtotals a
row of sales figures in that range using the =SUBTOTAL function. A
new set of sales figures comes out every week. But I am unable to
apply the same macro to the new sales figures because the number of
rows in the worksheet changes from week to week. The range in the
subtotal formula stays static while the numbers of rows is increased
or decreased each week. This causes the subtotal formula to only pick
up the range of data I originally asked for when I wrote the macro.

This is a copy of the macro…

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 4
Range("D2").Select
Selection.End(xlDown).Select
Selection.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "D"
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("E2").Select
Selection.End(xlDown).Select

Selection.Offset(2, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-327]C:R[-2]C)"

So if I run the macro the next week and the sheet has more or less
than 327 rows the macro only subtotals from E2 to E327.

Does anyone know how I can make the range that I subtotal relative to
where the offset starts back to the top of the column?

Thanks in advance,

Steve M.

 

[anilsolipuram]

you are putting static value 327, that represent the last row of dat
available.

What I have done is to identify the last row of data. I assumed that A
column does not have blank data., in your case it can any column.

so now can use determine last row and substitute last row value fo
327

here the code

Dim t As Integer
t = 0
Dim I As Integer
I = 1
While t = 0
Range("a" & I).Select
If Range("a" & I).Value = "" Then
t = 1
Else
I = I + 1
End If
Wend
MsgBox "last row:" & (I - 1

 

[Dave Peterson]

Another version...

Option Explicit
Sub testme02()
Dim myRng As Range
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a1:I" & LastRow)
.AutoFilterMode = False

.Columns("e:e").Insert
.Columns("E:E").ColumnWidth = 4
.Range("E1:E" & LastRow) = "D"

myRng.AutoFilter

Application.Goto .Range("a1"), scroll:=True
.Range("a2").Select
ActiveWindow.FreezePanes = True

.Cells(LastRow + 2, "G").FormulaR1C1 _
= "=SUBTOTAL(9,R[-2]C:R2C)"

End With
End Sub

I used column A to determine the lastrow--if you can't trust column A to have
data in it for all the rows, can you pick out a column that does always have
data? Adjust this line if you have to:

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

And I went from column A to column I as my data. Change this line to match your
last column:

Set myRng = .Range("a1:I" & LastRow)

I moved the autofilter to after the inserting of column D. (In versions prior
to xl2002, you can't insert/delete a column with filters applied.)

And you're filling that new column D with just D's??? I didn't get that, but it
was in your code.


And I just went down two rows from the last row and put in this formula:

.Cells(LastRow + 2, "G").FormulaR1C1 _
= "=SUBTOTAL(9,R[-2]C:R2C)"

in R1C1 notation, R2C means the same column, but row 2. It's kind of an anchor
point--it won't change.

Then r[-2]c means go up 2 rows in the same column.

I have written a macro that filters a range of data, then subtotals a
row of sales figures in that range using the =SUBTOTAL function. A
new set of sales figures comes out every week. But I am unable to
apply the same macro to the new sales figures because the number of
rows in the worksheet changes from week to week. The range in the
subtotal formula stays static while the numbers of rows is increased
or decreased each week. This causes the subtotal formula to only pick
up the range of data I originally asked for when I wrote the macro.

This is a copy of the macro…

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 4
Range("D2").Select
Selection.End(xlDown).Select
Selection.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "D"
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("E2").Select
Selection.End(xlDown).Select

Selection.Offset(2, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-327]C:R[-2]C)"

So if I run the macro the next week and the sheet has more or less
than 327 rows the macro only subtotals from E2 to E327.

Does anyone know how I can make the range that I subtotal relative to
where the offset starts back to the top of the column?

Thanks in advance,

Steve M.