Put Subtotal formula into first row, for width and depth of dataset

[2Took]

For a data set of variable number of columns and rows, trying to figure VBA to put a formula into first row for each column (preferably if it could also omit columns that contain text, but would be fine if it can't).

Formula needs to be calculating subtotals, showing only results for visible rows and not rows that are filtered out.

Such formula would be something like this =SUBTOTAL(109,F3:F50000). But the F3:F50000 needs to be variable for each column and number of rows, but can start with row 3 for each of the columns.

And color the cells in first row with those formulas yellow

Aside from this formula, no idea on the VBA syntax and how to make this happen. For some of you out there, I know -- it's a cinch.

 

[SanjayGMusafir]

For a data set of variable number of columns and rows, trying to figure VBA to put a formula into first row for each column (preferably if it could also omit columns that contain text, but would be fine if it can't).

Formula needs to be calculating subtotals, showing only results for visible rows and not rows that are filtered out.

Such formula would be something like this =SUBTOTAL(109,F3:F50000). But the F3:F50000 needs to be variable for each column and number of rows, but can start with row 3 for each of the columns.

And color the cells in first row with those formulas yellow

Aside from this formula, no idea on the VBA syntax and how to make this happen. For some of you out there, I know -- it's a cinch.

You are using Excel 365. Rather than using simple spreadsheet convert you data into Structured table using Insert Tab - Insert Table

After that all you need is

=SUBTOTAL(109,TableName[ColumnHeaderName])

You add any number of rows to that column it will give you the result

 

[Peter_SSs]

Such formula would be something like this =SUBTOTAL(109,F3:F50000). But the F3:F50000 needs to be variable for each column and number of rows,

Why does the number of rows need to change per column?
Subtotal 109 is a sum so any extra blank visible rows will not alter the sum. So if you make the 50,000 a number big enough to cover any amount of data you are likely to get, wouldn't that still work?

 

[2Took]

You are using Excel 365. Rather than using simple spreadsheet convert you data into Structured table using Insert Tab - Insert Table

After that all you need is

=SUBTOTAL(109,TableName[ColumnHeaderName])

You add any number of rows to that column it will give you the result

Thanks for the effort, but "trying to figure VBA...", not just a formula, different use case.

 

[2Took]

Why does the number of rows need to change per column?
Subtotal 109 is a sum so any extra blank visible rows will not alter the sum. So if you make the 50,000 a number big enough to cover any amount of data you are likely to get, wouldn't that still work?

Who said extra visible rows would be blank. I am not trying to make presumptions on how many rows there might be. I knew there is a way VBA can figure it out correctly every time.

So, following does exactly that. Now just looking for the remainder of my vision in OP.

And in this code need variation which would refer to the active sheet, rather than one with a specific name or even a code name, if you have idea how to edit this syntax?

Sub SubTotB1()
Dim LASTROW As Long
LASTROW = Sheets("abc").Cells(Rows.Count, "B").End(xlUp).Row
Range("B1").Formula = "=SUBTOTAL(109,B3:B" & LASTROW & ")"
End Sub

 

[2Took]

@Peter_SSs - Apparently following works fine, after testing it, to refer to the active sheet, rather than one with a specific name or even a code name.

Now just looking for the remainder of my vision in OP.

Sub SubTotB1()
Dim LASTROW As Long
LASTROW = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Range("B1").Formula = "=SUBTOTAL(109,B3:B" & LASTROW & ")"
End Sub

 

[Peter_SSs]

Who said extra visible rows would be blank.

Well, if you are using Cells(Rows.Count, "B").End(xlUp).Row then there must be nothing in any cells under the last data so using a big number would do the same thing.

 

[2Took]

Well, if you are using Cells(Rows.Count, "B").End(xlUp).Row then there must be nothing in any cells under the last data so using a big number would do the same thing.

"Big" is a relative term. No need to guess when XL can tell exactly.
And re: "Why does the number of rows need to change per column?" - it doesn't. First formula in will be on a column that dictates the depth of the data set, then per OP vision formula fills in for the rest of the columns.
See if you have solutions to the remained of OP vision. Thanks.

 

[Peter_SSs]

And re: "Why does the number of rows need to change per column?" - it doesn't.

Fair enough, but you could hardly blame us for thinking it did.

.. needs to be variable for each column and number of rows ..

Anyway, assuming your code from post #6 correctly finds the value for LASTROW (your data will need to be unfiltered to be sure of that) then all you should need to change in that code is

Range("B1").Formula = "=SUBTOTAL(109,B3:B" & LASTROW & ")"
Range("B1", Range("B2").End(xlToRight).Offset(-1)).Formula = "=SUBTOTAL(109,B3:B" & LASTROW & ")"