# Determine if range is empty

#### cdukes

##### New Member
Hello:

I've got a recursive loop stepping through rows to copy cells from Sheet A to Sheet B if they are present in Sheet A. I am currently struggling with a method of determining if a certain range of cells is empty. The range is from Cells(i, 60) to Cells(i, 101) or range("BH" & i ":CW" & i) [i is stepped through a For...Next].

My problem is that with the following code, the range always registers as empty, even when it is not.

For i = 2 to rinalrow

'Other code omitted

If IsEmpty(Sheets(Sheet A).Range("BH" & i & ":CW" & i)) = False Then
msgbox("range is empty")
end if

next i

I've also tried
If IsEmpty(Sheets(Sheet A).Range(Cells(i, 60), Cells(i, 110))) = False Then

Thanks in advance for any tips or suggestions.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Isempty can only test a SINGLE cell..
To test SEVERAL cells, I would count how many cells in the range are NOT blank, and if that result is NOT 0, then the range is not empty.
Rich (BB code):
``````If Application.CountA(Range(...)) > 0 Then
Msgbox "Range is NOT empty"
Else
Msgbox "Range is Empty"
End If``````

And another thing to consider is if the cells contain a formula returning "", this will NOT be considered blank.

Note, your logic here was backwards

Rich (BB code):
``````If IsEmpty(Sheets(report).Range("BH" & i & ":CW" & i)) = False Then
msgbox("range is empty")
end if``````

IsEmpty = False means the range is NOT empty...
IsEmpty = True means the range IS empty...

Hope that helps..

Jonmo1:

Thanks for the incredibly timely response. The counting method seems to have done the trick.

Many, many thanks.

Replies
1
Views
136
Replies
22
Views
486
Replies
10
Views
797
Replies
1
Views
281
Replies
1
Views
211

1,216,131
Messages
6,129,066
Members
449,485
Latest member
greggy

### 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.

### Which adblocker are you using?

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

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