Autosum at an empty cell in column. Only autosum for visible cells!

[Madter]

Hi guys.
I have a data sheet which is attached here.
I get a new data sheet like this every day, and that is why i need it to autosum where the red arrows are pointing in the attached picture.
I've used filter so i only need it for the visible cells.

I have this macro;

Sub AutoSum()
Dim Area As Range, MyColumn As String
MyColumn = "A"
    For Each Area In Columns(MyColumn).SpecialCells(xlConstants, xlNumbers).Areas
        SumAddr = Area.Address(False, False)
        Area.Offset(Area.Count, 0).Resize(1, 1).Formula = "=SUM(" & SumAddr & ")"
    Next Area
End Sub

..where i've tried substituting "xlConstants" with xlCellTypeVisible and deleting xlNumbers, but i can't seem to get it to work.
Any suggestions?

https://www.screencast.com/t/jwSSZNW1Us
Thanks

 

[JackDanIce]

Does the code provide the correct values in column H as you requested?

Why does the filter need to be re-applied?
Without knowing your spreadsheet, that is there in front of you, what are these filters you want re-applied?

Assume anyone reading is blind and cannot see your PC monitor, how would you precisely explain what you need doing then?

There are no delete commands in the code. Whilst I stated you didn't need to quote the entire thread back, it does help you're specific about what you're addressing!

[/COLOR].AutoFilter Field:=8, Criteria1:="<>"

Filters column H for non blanks
Then column A is filtered for value "N"

 

[Madter]

Okay, let me explain it from the beginning again, maybe more detailed
The only thing i need is for a Sumtotal or sum formula to be placed at the two empty cells in column H, as this picture illustrates.
https://www.screencast.com/t/jwSSZNW1Us

And it has to be a sum of the visible cells, not the hidden cells.

But the first code you made, did give me the right sum, but it deleted the filters, and i need those to still be in the sheet. So only a formula of sum in column H at the empty cells.
Hope it makes sense

 

[JackDanIce]

Why do the filters need to be applied?
Is there a reason you can't do this manually?
Have you tried to record a macro that re-applies the filter, if the first code does give you the right sum, as the initial purpose of your post?
Did you try @RoryA 's suggestion of using SUBTOTAL?

The code that gives the right sum is putting the sum value in the cells you require by only summing the visible cells and not using a formula. It's not clear why you're pointing out the blank cells to put the subtotal values in, if the code is summing the right values and from what I can tell, putting that value into column H, as how you have indicated.

You can adjust the code to switch back to a formula and/or incorporate with RoryA's suggestion

 

[Madter]

The filters are already there, so they don't need to be applied, but htey just need not to be removed. It's a part of my work, and i can't remove them. I already have recorded a macro putting a filter on and doing other things as well. The only thing i needed was the sum to be applied in the empty cells of column H, AS your first code did actually. But the problem with it was that it deleted my filters, which i of course need as mentioned before.

So how would a code with the SUBTOTAL look like?

And isn't there a way to modify the code i came with in the beginning, since it was working fine except for taking the sum of the visible cells.

 

[Madter]

UPDATE
I've used @RoryA'ssol​ution of using SUBTOTAL instead of SUM and it worked perfectly!! Thanks a lot!