Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totaling (Subtotaling) a Changing Filtered Range
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)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totaling (Subtotaling) a Changing Filtered Range
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. Corrie wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totaling (Subtotaling) a Changing Filtered Range
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- Dave Peterson wrote: 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. Corrie wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totaling (Subtotaling) a Changing Filtered Range
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. Corrie wrote: 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- Dave Peterson wrote: 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. Corrie wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totaling Volume within a range of dates | Excel Worksheet Functions | |||
Formula for totaling occurrences in a specified range | Excel Worksheet Functions | |||
Totaling range help needed | Excel Programming | |||
traversing through a filtered range based on another filtered range | Excel Programming | |||
Totaling & Subtotaling duplicate rows. | Excel Programming |