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]

Welcome to the board.

See if this helps:

Sub AutoSum()

    Dim LR  As Long
    
    LR = Cells(Rows.count, 1).End(xlUp).row
    
    MsgBox Application.SUM(Cells(1, 1).Resize(LR).SpecialCells(xlCellTypeVisible))

End Sub

 

[Madter]

Welcome to the board.

See if this helps:

Sub AutoSum()

    Dim LR  As Long
    
    LR = Cells(Rows.count, 1).End(xlUp).row
    
    MsgBox Application.SUM(Cells(1, 1).Resize(LR).SpecialCells(xlCellTypeVisible))

End Sub

Thanks! But no, it just gives me a pop-up message saying "0".

 

[JackDanIce]

Difficult to tell without a better understanding of your sheet or what you can see.

I filled column A with a mix of numbers and characters and blank cells, using a filter, removed the blank cells then ran the code and it summed the numbers only.

 

[Madter]

This is what i see. It just says "0". No changes

 

[Madter]

Does it show the attached picture?
https://www.screencast.com/t/1rpGfKzwt

 

[JackDanIce]

What are the criteria column A is being filtered on? I can only see letters in column A so the answer of 0 is correct.

If you were not using macro's, what would the formula be on the sheet? Ignore changing numbers of data rows.

 

[Madter]

The criteria column A is being filtered on is the letter "N", all other letters are deselected. And i need it on column H. And there is no formula on the sheet.

 

[JackDanIce]

In other words, sum column H if A = "N"?

Try:

Sub AutoSum()


    MsgBox Application.SUM(Range("H:H").SpecialCells(xlCellTypeVisible))


End Sub

 

[Madter]

Thanks for the input!
1. I need the autosum to be done in the cell and not as a messagebox.
2. I need the autosum to be inserted at the two empty cells in columns H. Exactly as the code i send in myself, but just for the visible cells