Totaling (Subtotaling) a Changing Filtered Range

[Corrie]

I am trying to total a range that is filtered to "X". My problem is
the range ("x"'d items) will change with new data. I was able to count
down to the last cell and execute a subtotal but it is a specified
range. Is there code I can use that will allow the range to be dynamic
(total whatever is filtered no matter the range)?

This is what I have...

Dim destCell As Range
Set destCell = Worksheets("6Pk").Cells(Rows.Count, "A") _

With destCell
.Offset(1, 0).Value = "Filled by 6Pk"
.Offset(1, 1).Value = "X"
.Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R[-335]C:R[-3]C)"

 

[Dave Peterson]

I'm not sure why you're going up 3 rows to find the last row to be included, but
maybe you could just modify that formula line:

..Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-3]C)"

R2C means row 2, same column.
R[-3]C means up 3 rows, same column.

I am trying to total a range that is filtered to "X". My problem is
the range ("x"'d items) will change with new data. I was able to count
down to the last cell and execute a subtotal but it is a specified
range. Is there code I can use that will allow the range to be dynamic
(total whatever is filtered no matter the range)?

This is what I have...

Dim destCell As Range
Set destCell = Worksheets("6Pk").Cells(Rows.Count, "A") _

With destCell
.Offset(1, 0).Value = "Filled by 6Pk"
.Offset(1, 1).Value = "X"
.Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R[-335]C:R[-3]C)"

 

[Corrie]

Thank you Dave. That worked beautifully and just that quick I hit
another road block. I've been reading through the postings trying to
find an answer. I've spent a couple of hours at it and haven't had
much luck...

I used the code: .Offset(1, 2).FormulaR1C1 =
"=SUBTOTAL(9,R2C:R[-3]C)". Now I need to take that formula and copy it
into the other cells (on the row) for each column affected by the
filter. I thought to do a count of columns but I believe I need to
specify a range or starting cell. Since the cell was the last row + 1
and 2 cell in (and it will change depending on the raw data) how do I
reference it? The number of columns will also be dynamic (sales person
dependent). One week I'll have 10 columns the next I may have 5 - each
of which needs a subtotal of the visible cells left after a filter
isolated some of the info.

It's probably very easy but I can't get my head around it...

Any help you can give is appreciated.

C-

I'm not sure why you're going up 3 rows to find the last row to be included, but
maybe you could just modify that formula line:

.Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-3]C)"

R2C means row 2, same column.
R[-3]C means up 3 rows, same column.

I am trying to total a range that is filtered to "X". My problem is
the range ("x"'d items) will change with new data. I was able to count
down to the last cell and execute a subtotal but it is a specified
range. Is there code I can use that will allow the range to be dynamic
(total whatever is filtered no matter the range)?

This is what I have...

Dim destCell As Range
Set destCell = Worksheets("6Pk").Cells(Rows.Count, "A") _

With destCell
.Offset(1, 0).Value = "Filled by 6Pk"
.Offset(1, 1).Value = "X"
.Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R[-335]C:R[-3]C)"

 

[Dave Peterson]

Maybe...

Dim destCell As Range
dim NumOfCols as long
with worksheets("6pk")
set destCell = .Cells(.Rows.Count, "A").end(xlup).offset(1,0)
numofcols = .autofilter.range.columns.count
end with

With destCell
.Value = "Filled by 6Pk"
.Offset(0, 1).Value = "X"
.Offset(0, 2).resize(1,numofcols-2).FormulaR1C1 _
= "=SUBTOTAL(9,R2C:R[-3]C)"
end with

numofcols-2 because you put stuff in the first two columns.

Thank you Dave. That worked beautifully and just that quick I hit
another road block. I've been reading through the postings trying to
find an answer. I've spent a couple of hours at it and haven't had
much luck...

I used the code: .Offset(1, 2).FormulaR1C1 =
"=SUBTOTAL(9,R2C:R[-3]C)". Now I need to take that formula and copy it
into the other cells (on the row) for each column affected by the
filter. I thought to do a count of columns but I believe I need to
specify a range or starting cell. Since the cell was the last row + 1
and 2 cell in (and it will change depending on the raw data) how do I
reference it? The number of columns will also be dynamic (sales person
dependent). One week I'll have 10 columns the next I may have 5 - each
of which needs a subtotal of the visible cells left after a filter
isolated some of the info.

It's probably very easy but I can't get my head around it...

Any help you can give is appreciated.

C-

I'm not sure why you're going up 3 rows to find the last row to be included, but
maybe you could just modify that formula line:

.Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-3]C)"

R2C means row 2, same column.
R[-3]C means up 3 rows, same column.

I am trying to total a range that is filtered to "X". My problem is
the range ("x"'d items) will change with new data. I was able to count
down to the last cell and execute a subtotal but it is a specified
range. Is there code I can use that will allow the range to be dynamic
(total whatever is filtered no matter the range)?

This is what I have...

Dim destCell As Range
Set destCell = Worksheets("6Pk").Cells(Rows.Count, "A") _

With destCell
.Offset(1, 0).Value = "Filled by 6Pk"
.Offset(1, 1).Value = "X"
.Offset(1, 2).FormulaR1C1 = "=SUBTOTAL(9,R[-335]C:R[-3]C)"