# ISBLANK() WITH MULTIPLE RANGES

#### cheveley

##### Active Member
hi

does anyone know if it is possible to use the isblank() formula with multiple ranges i.e. isblank(h1:z40) returns false even if the cells are blank. once again i am in the dark.

thanks

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I don't think so,

But you can do something like this

(H1:Z40)="", confirmed with Ctrl+Shift+Enter, not just Enter.

Hi NBVC, that only gives the answer for the first cell in the range.

To find out if the entire range is blank, use:
Code:
``=AND(ISBLANK(H1:Z40))``
entered using Ctrl-Shift-Enter instead of Enter.

works a treat, thanks.

GlennUK said:
Hi NBVC, that only gives the answer for the first cell in the range.

To find out if the entire range is blank, use:
Code:
``=AND(ISBLANK(H1:Z40))``
entered using Ctrl-Shift-Enter instead of Enter.

Hi Glenn,

Although, I do admit I like your formula better, I re-tested my formula again and it works just like yours (i.e. If any cells are blank in the range, False response is returned and if All cells are blank a True is returned.)

Hi NBVC,

test your tests again. For your formula, if the first cell is blank a TRUE is returned, even if there are non-blank cells in the range. So, your formula does not work just like mine.

how would one extend =AND(ISBLANK(H1:Z40)) to

=AND(ISBLANK(H1:Z40,aa430;ac450))

i.e. another range in the formula. is it possible.

thanks.

GlennUK said:
Hi NBVC,

test your tests again. For your formula, if the first cell is blank a TRUE is returned, even if there are non-blank cells in the range. So, your formula does not work just like mine.

I guess you're right. Sorry. That's one I didn't try.

cheveley said:
how would one extend =AND(ISBLANK(H1:Z40)) to

=AND(ISBLANK(H1:Z40,aa430;ac450))

i.e. another range in the formula. is it possible.

thanks.

I would say: =AND(ISBLANK(H1:Z40),ISBLANK(AA430:AC450))

Hi cheveley, you extend the number of ranges tested by nesting the AND functions, like this:
Code:
``=AND(AND(ISBLANK(F3:H3)),AND(ISBLANK(J2:L2)))``

Hi NBVC,
no problem, just making sure that the right formula gets used.

Edited:
Hi NBVC,
your final post is great! I've got more ANDs than necessary.

Replies
7
Views
69
Replies
5
Views
397
Replies
3
Views
86
Replies
0
Views
120
Replies
3
Views
192

Threads
1,202,990
Messages
6,052,962
Members
444,622
Latest member
Kriszilla

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back