Intentionally referencing an empty cell

[Mr_Ragweed]

Hi and thanks in advance,
I'm working on an "If Then" issue. In laymans terms my code would read "If this range of cells is empty, then copy this other range of cells".

In my attempt to code it it reads:
If ActiveSheet.Shapes(btnAccept").Select = Click Then
If ActiveSheet.Range("I4:I13") = IsEmpty Then
ActiveSheet.Range("B1" & "N4:N13").Select
Selection.Copy
yadda yadda yadda

My problem is with the "IsEmpty". Apparently thats not the right nomenclature. I've tried "Empty", "Blank", and "IsBlank" as well - all to no avail. How do I reference/what do i call an empty cell?

Thanks again.

 

[Jerry Sullivan]

Hi Mr_Ragweed,

Here's one way to test if the range is empty...

Sub Test()
 If Application.CountA(Range("i4:i13")) = 0 Then
   MsgBox "Range is empty"
   '---your copy code
   '....
 Else
   MsgBox "Range is not empty"
 End If
End Sub

 

[Mr_Ragweed]

Thanks Jerry,
I discovered a work around as there is 1 cell in the range that will be populated and i just reference that cell instead of the range. Most of my data lengths are variable and this particular range happens to be a fixed length so it worked.

Thanks again!

Just curious though as i may need it in the future. how do a reference a cell or range that i KNOW is empty? And being empty is the condition i need to continue the "If Then Else" statement....

 

[Jerry Sullivan]

Just curious though as i may need it in the future. how do a reference a cell or range that i KNOW is empty? And being empty is the condition i need to continue the "If Then Else" statement....

I'm not sure I understand your question exactly. The reference to a cell or range would be the same expression whether or not the cell is empty.

For example, you could change the fill color of a range of cells like this (regardless of whether the range is empty).

Range("A4:A13").Interior.Color=vbYellow

Please clarify if I misunderstood your question.

 

[Mr_Ragweed]

Jerry, Again many thanks for your assistance on this and other questions i've had.
I can reference the range or cell with no issue i'm just getting hung up on the condition. What is the syntax for "If condition = empty, Then..."
(Again, i'm beyond this point of the project. I'm just asking now for my own edification if you have the time.)