Formula keeps changing

[Godders199]

Hello, I have a puzzle that I cannot solve,
I have the following formula to count the number of nonblank cells in a column
="Check Type "&COUNTIF($AA$2:$AA$7500,"<>")
The spreadsheet is refreshed daily and I use thefollowing code to clear out the dataprior to the refresh.
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Range("A2:Ay50000").Cells.Clear

However after the code has fully run and I check the aboutformula it has reduced the number of rows it is looking at.
="Check Type "&COUNTIF($AA$2:$AA$7497,"<>")
As I am using $$ I did not think the formula can change.
Am I being silly?

 

[tyija1995]

Hello,

The $ refers to an absolute reference, so that when you copy the formula across it won't change - 1 reason why it's going from 7500 down to 7497 could be that 3 rows are being deleted between 2 and 7500.

 

[JackDanIce]

It's not clear where the fomulas are i.e. cell address. Try this to begin with:

Sub mTest1()

    Dim LR  As Long
    Dim LC  As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Cells(2, 1).Resize(LR - 1, LC).Value = ""
    End With
    
     With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
End Sub

I think .Cells.Clear "deletes" the cells entirely, whilst .Value = "" only chages the cell values. This could explain why your formula range changes.

 

[Godders199]

Bingo, at the end of the VBA it does delete unrequired rows, so rather than leave the formula in the cell, i just need to add it to the coding to count to last row. As it was part of the template i did not even consider the rows being deleted.

thanks

 

[JackDanIce]

You're welcome.. I think!