# 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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Jonmo1

##### MrExcel MVP
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
Jonmo1:

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

Many, many thanks.

1,106,503
Messages
5,511,697
Members
408,859
Latest member
willm57

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...