Get Macros To Overwrite Total On Same Cell

[NewbieOfVBA]

Hello,

I have this code that counts the # of occupied cells and inputs the count into an empty cell in column F.

Sub CountOccupiedCells()
    Dim nRow As Long
    Dim nCol As Long
    Dim nCount As Long
    
    For nRow = 2 To ActiveSheet.UsedRange.Rows.Count
        For nCol = 1 To ActiveSheet.UsedRange.Columns.Count
            If Not IsEmpty(Cells(nRow, nCol)) Then nCount = nCount + 1
        Next nCol
    Next nRow
    Range("F" & Cells(Rows.Count, "F").End(xlUp).Row + 1) = nCount
End Sub

How do i change this so that the macros puts the count total in cell F3 everytime (even if it has to overwrite the last total it counted).

I want it to do this because the occupied cells its counting could increase, and I want only 1 updated total to appear when i run the macros after i've added more entries. Because right now it puts the new total under the last total.

thanks!

[wakeupcall]

Hi,

I believe this should do the trick

Sub CountOccupiedCells()
    Dim nRow As Long
    Dim nCol As Long
    Dim nCount As Long
    
    For nRow = 2 To ActiveSheet.UsedRange.Rows.Count
        For nCol = 1 To ActiveSheet.UsedRange.Columns.Count
            If Not IsEmpty(Cells(nRow, nCol)) Then nCount = nCount + 1
        Next nCol
    Next nRow
    Range("F3") = nCount
End Sub

[NewbieOfVBA]

Hello,

Thank you for your reply.
I tried what you gave me with just a few simple numbers to make sure it worked and it did perfectly. The problem is when I applied it to the workbook I needed it to, It didn't fill the same cell like it did with my example, it filled the cell underneath the previous one again.

Not sure why, does anyone know?

Thanks

[wakeupcall]

Hi,

this is probably because the used range in the second excel workbook is greater than it appears at first sight.

Press Ctrl+End and see where it takes you. If it takes you to a cell that is way below the last one occupied, you should select the seemingly empty cells and clear all/delete the column/row.

Re-running the macro after that should work properly