Button to search for empty consecutive cells.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Test this.
I select columns B:G and run the macro and it tells me employee 1 didn't have 6 consecutive cells empty cells.
I don't know exactly how you are going to use this, so if it does something you don't like be specific about what you selected etc. and what it did. Specific, not I selected 8 cells and it didn't work. I selected cells (A1:G1) and it did this and I expected this to happen and why.

I was able to test and it seems very close. however if I select several days B:AW, it does not find that Employee 3 had 6 consecutive empty cells (B:G).

Error3.jpg

This was one of my concerns when I said I didn't know how you were going to use this. I considered this, but wrote the code so that if they work any hours during the period you select then it starts over with the counting. I thought your concern was their latest entries, not whether at anytime during the period selected on your sheet they had six consecutive blank cells. The other thing that can cause problems would be if you don't select a full day. In other words you select E:I which is partly in one day and partly in another. The code isn't segregating days. If you say I won't do that I will only select whole days and I am only concerned if they ever had six days during any of the whole days during the period I pick, then I will work under that assumption.

The users will we able to select any period of time but generally they would be selecting 5 to 7 days worth of columns. having 6 consecutive blank cells, even if 3 are on one day and 3 are on the next is ok as 6 consecutive cells is still true. so possibly counters for blank and nonblanks cells??

So just a couple of things you should know about the code.
I changed it so that as you add employees it will automatically adjust. This code assumes your employees are in column A and that the area below the employees in col A is clear all the way to the bottom of the sheet, no other data. It also assumes the employees start in row 5 as they do on your sample sheet. It's obvious where to change that if you move the first employee to a different row.
So 5 is the starting row, the code checks for the last row with data in it in col A to decide where to stop that is the variable you see as lr.
Here is that code. Also notice the worksheet name, change it to suit your actual sheet name, the quotes need to stay around the name. These are two separate lines is the code, I'm just drawing your attention to these lines. The code looks to see if the columns you selected intersect with any employee rows, so selecting data not on an employee row won't yield any results.

Please Login or Register  to view this content.

The code now looks for any six consecutive blank spaces within your selection. The counter starts at zero and adds 1 for each blank cell it finds. It resets every time it finds a cell that is not blank. If it reaches 6 it just moves to the next employee and you will not get a message box unless it actually finishes all selected cells for a given employee and hasn't reached 6, it may have reached 5 and then reset but since it never got to 6 you will now get a message. Bottom line 6 blank cells in a row anywhere in each row you selected and you get no message.

I think I got it. I wrote this before I seen your last reply. I also had to add an If statement that would skip blank is column A that are not real employees.

Please Login or Register  to view this content.

Please click on the edit post button, select all of your code, then click the # icon and choose save changes.

Code tags are required when posting code.

Your code will go from this:
Sub Test()
End Sub

To this:

Please Login or Register  to view this content.

Originally Posted by dam6952

I did it! After further testing the code i made is slightly off. I will have to check out your new revision next week and get back to you. thank you for all your help!

Good for you.
Make sure to share the code that worked for you as this is a learning forum and those who peruse it with a similar need would like to see what worked.

Then when you are happy with your solution.

Please click the Thread Tools drop down box above your first post and choose solved.

And if you are happy with my help then please consider clicking the add reputation button in the bottom left hand corner of any of my posts.

Your code is working perfectly. I wish I could have an explanation as to how it works. Thanks Again!