CODEWITHSUNDEEP
vbaexcel
user3325783
user3325783

Reputation:

Checking to see if two cells are empty

I'm trying to see if two cells are NOT empty in VBA. I'm using the IsEmpty() but I don't think I'm using them right. This is what I have. It's not working because it will always go into the if statement.

Inside my if, I'm manipulating some cells. I shouldn't manipulate them if I go into the else.(Which does nothing), but they end up changing also.

For i = 2 To rows 
        If ((Not IsEmpty(Cells(i, 4))) And (Not IsEmpty(Cells(i, 5)))) Then
            'Do stuff...
        else
            'Do nothing...
        end if
next i

Is this the best way?

Upvotes: 0

Views: 862

Answers (3)

TheDefiant89
TheDefiant89

Reputation: 35

The method I normally go for is to check the cell referenced, to a type. As far as I'm aware, when you reference a cell, the interpreter will return a type which best suits the value, i.e. 1 returns a Double, "abc" returns a String and so on. When a cell is blank, the type returned is Empty.

I carried out a performance test using the technique mentioned here.

Test 1: Using Len() to compare the cells took 40.9080069854244

dTime = MicroTimer
For i = 1 To 10000
  If Len(Cells(i, 4)) And Len(Cells(i, 5)) Then
    'Do something
  Else
    'Do nothing  
  End If
Next i
dTime = MicroTimer - dTime '40.9080069854244

Test 2: Comparing the cells to the type Empty only took 21.0493610010926

dTime = MicroTimer
For i = 1 To 10000
  If Cells(i, 4) <> Empty And Cells(i, 5) <> Empty Then
    'Do something
  Else
    'Do nothing
  End If
Next i
dTime = MicroTimer - dTime '21.0493610010926

It may not look as clean, but if definitely has a benefit.

Upvotes: 0

user6432984
user6432984

Reputation:

These are all valid

If ((Not IsEmpty(Cells(i, 4))) And (Not IsEmpty(Cells(i, 5)))) Then

If Cells(i, 4) <> "" And Cells(i, 5) <> "" Then

If Len(Cells(i, 4)) And Len(Cells(i, 5)) Then

If Len(Cells(i, 4)) > 0 And Len(Cells(i, 5)) > 0 Then

My personal preference is to use Len(). I think it's a clean look.

Wrapping your code in a with statement will make it easier to compare ranges on different worksheets.

With managerSheet

    For i = 2 To rows 
            If  Len(.Cells(i, 4)) And Len(.Cells(i, 5) Then
                'Do stuff...
            else
                'Do nothing...
            end if
    next i

End With

Upvotes: 1

user3325783
user3325783

Reputation:

My problem was not my if statement but rather the scope of my calls. I forgot to include the scope of cells. managerSheet.Cells(i, 4).. etc Thanks for the help

Upvotes: 0

Related Questions