Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel: How to check if a cell is empty with VBA? [duplicate]

Tags:

excel

vba

Via VBA how can I check if a cell is empty from another with specific information?

For example:

If A:A = "product special" And B:B is null Then

C1 = "product special"

Illustration of example

Additionally, how can I use a For Each loop on theRange and how can I return the value in the other cell?

like image 966
Regis Santos Avatar asked Nov 13 '12 12:11

Regis Santos


People also ask

How do you test if a cell is empty in Excel VBA?

If you wish to test whether a worksheet cell is empty in VBA, you can not use the worksheet function called ISBLANK. In VBA, you must use the ISEMPTY function. In this example, we will test whether cell A1 is empty. If cell A1 is empty, the message "Cell A1 is empty" will be displayed.

Is empty in VBA?

VBA IsEmpty is a logical function that tests whether selected is empty or not. Since it is a logical function it will return the results in Boolean values i.e. either TRUE or FALSE. If the selected cell is empty it will return TRUE or else it will return FALSE.

How do you check if a cell is empty in Excel?

The ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE. Use the ISBLANK function to test if a cell is empty or not. ISBLANK function takes one argument, value, which is a cell reference like A1.


3 Answers

You could use IsEmpty() function like this:

...
Set rRng = Sheet1.Range("A10")
If IsEmpty(rRng.Value) Then ...

you could also use following:

If ActiveCell.Value = vbNullString Then ...
like image 160
Sylca Avatar answered Oct 23 '22 15:10

Sylca


IsEmpty() would be the quickest way to check for that.

IsNull() would seem like a similar solution, but keep in mind Null has to be assigned to the cell; it's not inherently created in the cell.

Also, you can check the cell by:

count()

counta()

Len(range("BCell").Value) = 0

like image 37
Deafdan Avatar answered Oct 23 '22 14:10

Deafdan


This site uses the method isEmpty().

Edit: content grabbed from site, before the url will going to be invalid.

Worksheets("Sheet1").Range("A1").Sort _
    key1:=Worksheets("Sheet1").Range("A1")
Set currentCell = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(currentCell)
    Set nextCell = currentCell.Offset(1, 0)
    If nextCell.Value = currentCell.Value Then
        currentCell.EntireRow.Delete
    End If
    Set currentCell = nextCell
Loop

In the first step the data in the first column from Sheet1 will be sort. In the second step, all rows with same data will be removed.

like image 34
Reporter Avatar answered Oct 23 '22 15:10

Reporter