Subtotal with Macro

Bookmaster

Board Regular
Joined
Mar 22, 2014
Messages
73
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I am desperate now. I spent last 4 hours trying to solve one SUBTOTAL function but it won't work. I have one workbook with just one sheet. In that sheet I have a lot of data. Now I need to put Subtotal that will make sum of all numbers that I have in row "H" starting from "H2" up to the end of row where I have data. End of row may vary and I never know which cell will be the last cell.
Is there any help please?

BM
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Bookmaster

Board Regular
Joined
Mar 22, 2014
Messages
73
Office Version
  1. 2013
Platform
  1. Windows
I manage to find something over net for SUM (not for subtotal) but that might be a start.
Code looks like this:

"
LASTROW = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 7).End(xlUp).Row
ThisWorkbook.Sheets("Sheet1").Range("G" & LASTROW + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("G2:G" & LASTROW))
End Sub"

But it doesn't do the job. What I am doing wrong?
Can somebody help me please?

BM
 

Bookmaster

Board Regular
Joined
Mar 22, 2014
Messages
73
Office Version
  1. 2013
Platform
  1. Windows
For everyone that might need it, this code is:

"Range("H2").Select
Cells(Rows.Count, "H").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R2C:R[-1]C)"
End Sub"

BM
 

Watch MrExcel Video

Forum statistics

Threads
1,109,484
Messages
5,529,135
Members
409,851
Latest member
Ingar
Top