Determine if range is empty

cdukes

New Member
Joined
Jul 26, 2010
Messages
8
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..
 

cdukes

New Member
Joined
Jul 26, 2010
Messages
8
Jonmo1:

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

Many, many thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,040
Messages
5,656,531
Members
418,313
Latest member
TrinityGal

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

Which adblocker are you using?

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
Top