Check for blank cells in variable range

[SueBK]

I feel like I spend more time on here than actually working, but without the time on here I wouldn't be working at all. Anyhoo...

I have a set of worksheets with data broken into states. Each state is 'allowed' to add columns to the end of my dataset but they are not allowed to change my column structure in anyway (add, delete, rearrange). They are also not supposed to add new rows without me giving them an ID number for column A. Each worksheet will have a different, and variable number of rows from day-to-day.

Every morning I need to merge these worksheets into a single sheet. This is just one question that I have for the process that I've devised. (I figure breaking them down into separate posts would be easier to answer.)

As the first step of my macro I would like to check Column A for blank cells. There is always going to be data in Column B (site name), but a user may not have asked for a new record ID number. I'm thinking maybe the simplest way would be :

if sheet("1") - countif(B:B) <> countif(A:A) then
msg box "sheet 1 requires ID numbers"
and end macro
else
repeat for each sheet

Seems straight forward enough, but I'm not sure how to begin.

 

[SueBK]

I've gone with a hidden sheet that with 'countif' on my Col B and 'countif' on my Col A. If B-A>0, the macro starts with a message box to fix it and cancels out. The information has to be fixed before proceeding, so it makes more sense to just cancel out the macro immediately. The hidden sheet then tells me which sheet the issue is on.