Detect first and last row and count blanks in between

Hi all, apologies in advance if this has been covered in previous threads but my search came up unsuccessful.

I have attached an example sheet.
I basically need to count the blank cells in the range from when the first number at the top starts to the last number.
So in col1 would be 2 in col2 would be 4.
Ideally just counting them or as an alternative fill with a value like a “Z”.
There are other columns on the right and so this will carry on, for example, until column Z.

Many thanks 😊

Thanks Marvin but I was looking for something to be executed with a code and to be neater. I believe it is possible with first and last row search and loop until blanks are found, or something similar.

Please Login or Register  to view this content.

Musto85,

As I don't know how/where you want the result, so this is to just a demo,
In order to distinguish when no number and only one number in the column, it will give Error when no number and 0 for only one number.

Please Login or Register  to view this content.

Thanks nimrod1313 you star !

Can this be looped for all the column from B to Z for example?
And can the count be shown in a cell instead of the msgbox please?
Last cell just below the last hour on each column will do.
Thank you very very much

Update: I’ve sorted the count on the cell instead of msgbox.

Still need looping for the other columns

That’s wonderful.

Thank you so much sir!

Another angle of attack.
Total amount.

Please Login or Register  to view this content.

Individual Columns.

Please Login or Register  to view this content.

Thanks MarvinP and jolivanes. That’s fantastic guys, thank you all so much.

How would I go about if I want those empty cells in the middle to be filled with the average between the number above and below?

I found something like this but only works for all the empty cells

Sub Test()
Dim i As Long, j As Long, n As Long
Application.ScreenUpdating = False
n = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To n - 1
If Cells(i, "A") = "" Then
j = i
Do While Cells(i, "A") = ""
i = i + 1
Loop
Range(Cells(j, "A"), Cells(i - 1, "A")) = (Cells(j - 1, "A") + Cells(i, "A")) / 2
End If
Next

Application.ScreenUpdating = True
End Sub

Please Login or Register  to view this content.

Re: "those empty cells in the middle to be filled with the average between the number above and below?"

So maybe.

Please Login or Register  to view this content.

jolivanes: good try, it did fill with the average the cells in the middle but it also filled all the other cells after the last row.

[QUOTE=jindon;5675217]


jindon I struggle to understand your code but it’s art and works brilliantly!
Is there a way that can be slightly adjusted to give the total of blanks cell for each column at the bottom instead of the message box?
Thanks in advance

Re Post #17
Maybe you should look at your workbook at find out where your last used cell is.

Did you change anything in the macros?
When you say "filled all the other cells after the last row", what do you mean by that? Do the cells at the last row level have that total amount of empties in it?
If things don't work they way you think it should work is normally because a lack of communication/proper explanation.
You have the workbook in front of you while all we have is the attachment from Post #1 which might be close to reality but quite often things are different.

If you run this first

Please Login or Register  to view this content.

and this after

Please Login or Register  to view this content.

does it work?

Originally Posted by Musto85

Is there a way that can be slightly adjusted to give the total of blanks cell for each column at the bottom instead of the message box?

Try change to

Please Login or Register  to view this content.

That’s great thank you so much works brilliantly.
Is there a way that the averages can be rounded down to the nearest whole
i.e 4.5 to 4,
2.5 to 2….

Amazing.
Last one I promise
I can’t make it run for various sheets at the same time
My sheets are called:
Mon
Tue
Wed
Thu
Fri
Sat
Sun

Replace "test" sub procedure with below

Please Login or Register  to view this content.

Brilliant! Thank you jindon💪🏻

Thanks to jolivanes too and all involved 👌🏼

I will have to reopen this thread as I’m now facing another challenge…
I have attached a sheet as example.

As you can see from the attachment I have another set of data at the bottom (A35:U59) with numbers in between certain time intervals.
What I would like to obtain is comparing the data at the bottom to the data at the top (A1:R25) ensuring the relevant columns correspond with the label row i.e. LAR01 with LAR01, LAR06 with LAR06 etc…(as this can be misplaced in different columns) fill with colour where the corresponding interval has no data and also the average between first and last number where the interruption is.

I.e. LAR01 (B35:B59) has numbers between 6:00pm and 1:00 am, on the data above same column corresponding label LAR01 there is data missing at 9:00pm, 10:00pm and 01:00am and so on for all the columns (I’eve filled as example until LAR19.


Sorry for the explanation, please feel freee to ask for more examples if needed.

Thank you in advance!!

Originally Posted by Must85

also the average between first and last number where the interruption is.

I don't understand the calculation above.

This is to only color the cell(s).

Please Login or Register  to view this content.

I couldn't expect anything less than a work of art from you jindon!

That works perfectly thank you.
Now I would need to count those highlighted cells placing the total for each column at the bottom (B27,C27 etc..) and fill these highlighted cells with the average between the numbers on the above and below cells i.e. B17 and B18 = average between 5 and 0.
B21 = average between 4 and 0 and so on...

I used this code that you provided before that was doing exactly that but I believe will need tweaking...

Please Login or Register  to view this content.

If you upload a workbook with before/after sheets clearly showing how you want, it will be clear.

Please see attachment.

As you will notice I'm trying to avoid filling with zeros... so when number above and below are zeros it will have to fill with a 1 instead of zeros.

See if this is how you wanted.

Please Login or Register  to view this content.

The average and the count at the bottom works fine however it also fills the cells that are not highlighted in red.


i.e. I12:I15 have been also filled with average
M14:M17 too...

Try this

Please Login or Register  to view this content.

Brilliant, thanks jindon!

Is there a way to have a message box appearing for every single hour that shows all those numbers in the highlighted cells with the corresponding label?
i.e.
6:00am no msgbox to shown
07:00
LBJ07 = 1
LPB07 = 1
11:00 am
LTCP01 = 1
12:00pm
LPB02 = 3

...and so on for every hour, skipping where there are no highlighted cells.
When Pressing "ok" on the msgbox it will close and the next one will open.

try

Please Login or Register  to view this content.

Thanks jindon

Instead of going by column can it go by row and therefore by each time interval:

06:00:
07:00: LBJ07 = 1, LPB01 = 1
08:00:
09:00:
10:00:
11:00: LTCP01 = 1

And so on…

Please Login or Register  to view this content.

Great, but the code before for some reasons was showing the msgbox right after highlighting and filling all the cells with average.
This last code instead it highlights everything and fills with average after clicking “ok” on the msgbox.

Is there also a way to create a new sheet with the info contained in the msgbox?

Thanks for helping me out with this jindon much appreciated.