Determine if range is empty

[cdukes]

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.

 

[Jonmo1]

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.

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

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]

Jonmo1:

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

Many, many thanks.