Counting unique and non-blank cells using a VBA code

[sakate123]

Hello,

I am a new VBA user and I am trying to write a code to count values in a column. My data is dynamic and updates on a 24hr refresh and the data can be formatted slightly different each time it is updated. Typically the format of the values follows a pattern like this:

Where each alternating row is a blank. However, sometimes the data will have extra blank rows between the next occupied cell. (see below)

The values are always ascending, but each day at midnight the value resets to 1 and could potentially reach the same value that was already seen in the previous 24hrs. So a simple unique value formula will not work.
Eg. Aug 8, 2020 3:00AM: 110
Aug 9, 2020 1:15AM: 110

Basically what I need is a way to count every time the value increases while skipping over the blank cells (in pic 2 total count would be 3, exclude the 2nd 299).
I have a code that puts a value of 1 beside the cells with an increasing value and excludes any repetitive numbers but it does not work when there are extra blank cells in the data. Here is the code. (PiTag starts recording infomation in row 12)

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So, IF you have Excel 365, try something like ..

Sub Count_Unique()
  With Range("BQ1", Range("BQ" & Rows.Count).End(xlUp))
    MsgBox Evaluate(Replace("COUNT(UNIQUE(FILTER(#,#<>"""")))", "#", .Address))
  End With
End Sub

 

[sakate123]

Hi Peter,

Thanks for getting back to me. I have updated my account details.

Would you be able to explain the function a little more?

 

[Peter_SSs]

I have updated my account details.

Thanks.

Would you be able to explain the function a little more?

Take this example

sakate123 2020-08-11 1.xlsm
	BQ
1	299
2
3	299
4	299
5
6
7
8	484
9
10
11	485
12
Sheet1

With Range("BQ1", Range("BQ" & Rows.Count).End(xlUp))
This would be the range BQ1:BQ11 for the above sheet

Evaluate(Replace("COUNT(UNIQUE(FILTER(#,#<>"""")))", "#", .Address))
For that range this becomes
Evaluate("COUNT(UNIQUE(FILTER(BQ1:BQ11,BQ1:BQ11<>"""")))")

FILTER(BQ1:BQ11,BQ1:BQ11<>"""")
Return all the values from BQ1:BQ11 where the cell is not empty
{299,299,299,484,485}

UNIQUE({299,299,299,484,485})
Returns {299,484,485}

COUNT({299,484,485})
Returns 3