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.
 

Some videos you may like

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top