Msgbox all empty rows in a range

[nickmax1]

I have a range B18:B190 that can be populated from time to time either fully (ie all rows are used) or sometimes only B18:B100 for example are populated.
This works great but on occasion I notice that there are a few blank rows thrown into my range by colleagues of mine who insert/delete rows (which I do want them to be allowed to do) but I would like to have a macro I can run that gives me a messagebox telling me which rows in my range have blank rows.
I do not want the messagebox to tell me about the blank rows that exist after my last row of data though, as in the example above I dont want to know about rows 101-190 as they are meant to be blank...

as a more thorough example, let me say that there were populated rows of B18:B50, row 51 was blank, B52:B100 were populated, row 101 was blank and B102:B150 were populated and then B151to B190 were blank (as they are supposed to be as that is the end of the data) I would want a messagebox to say
"Blank rows in 51 and 101"

any ideas?

[Olly]

Try:

Please Login or Register  to view this content.

[nathansav]

Please Login or Register  to view this content.

[nickmax1]

Olly - that works great, but only if there are blank rows to find. - how do you change it so that it doesnt crash if there are NO blank cells?

Nathansav - I get a syntax error on the line

Please Login or Register  to view this content.

[nathansav]

add a space between s and &

[nickmax1]

nathansav,
ah thats better. However it still returns all the blanks from the last used row in my range to 191 which I dont want as they are supposed to be blank in that case.

EDIT: fixed that with

Please Login or Register  to view this content.

[Olly]

Olly - that works great, but only if there are blank rows to find. - how do you change it so that it doesnt crash if there are NO blank cells?

Adding a bit of error handling:

Please Login or Register  to view this content.

Also tweaked to return just the rows, not the full cell addresses.

[nickmax1]

both Olly and Nathansav's code works but only when my range is entirely unhidden (or unfiltered) ?

[Olly]

My code in post #7 seems to work fine for me, regardless of hidden / filtered rows.

Can you attach a workbook demonstrating what's not working right?

[nickmax1]

OK.
See attached. As it is your macro will run nicely and identify row 141 as being blank. But if you hide say rows 138-150 and run the macro it wont find it...

[Olly]

I see now. The end(xlup) skips past the hidden rows at the bottom of your used range. So we need to find the last used cell in column B a slightly different way:

Please Login or Register  to view this content.

Better?

[nickmax1]

Olly
Fantastic that solves that problem, but now the macro identifies the empty cells between the last used row of the specified range to 191 as well which we dont want.

[Olly]

It doesn't do that for me...

In the file you attached in post #10, I run the following code:

Please Login or Register  to view this content.

And it returns the following msgbox:

"Blank cells in 141"

[nickmax1]

driving me nuts, it works fine on the test one but not on the master....so here it is...why doesnt it work here?

i am expecting it to return "row 164 and 165 are blank" or similar.

[Olly]

Ah - different corresponding data in column A!

Change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

[nickmax1]

works !! yes!!

One final - once it has determined the rows, any way you can add code to clear the contents of said rows?

[nickmax1]

may have figured it out with:

Please Login or Register  to view this content.

[Olly]

Yes, that would do. It's one of the reasons why the range object 'r' was created, to make it easier to do something with, as well as just the message box.

[nickmax1]

you are a star. rep added. thread solved.