CODEWITHSUNDEEP
vbaexcel
Makciek
Makciek

Reputation: 11

Checking for null in cells

Code:

    Sub Usuns()
Dim wiersz, licznik As Integer
wiersz = 0
licznik = 0
Do
licznik = licznik + 1
 If Range("B" & licznik).Value Is Null Then
  Range("B" & licznik).Select
  Selection.Delete
  wiersz = wiersz + 1
 Else
  wiersz = 0
 End If
 If wiersz = 50 Then
       Exit Do
 End If
Loop
End Sub

This line If Range("B" & licznik).Value Is Null Then throws this error:

Run-time error '424': Object required

How do I fix this?

Upvotes: 1

Views: 226

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

#NULL!

I think you're looking to detect whether a cell contains the Excel error code #NULL!. To do this you need to use the CVErr function:

If Range("B" & licznik).Value = CVErr(xlErrNull)

Empty cells

If, however, you want to test for empty (blank) cells, you'll want to use the IsEmpty function. 

If IsEmpty(Range("B" & licznik).Value) Then

Note that this is the only way to strictly test for blank cells. Other tricks will not behave in the same way; for instance, Range("B" & licznik).Value = "" will return True if the cell contains an empty string (i.e. ="") whereas IsEmpty will return False. Depends what you want to do. Choose cautiously.

Upvotes: 1

JMax
JMax

Reputation: 26591

You cannot use the value Null this way.

You should try:

If Range("B" & licznik).Value = ""

See here for a small discussion about Null.

Upvotes: 1

Widor
Widor

Reputation: 13275

I suspect that Range needs to be in scope of its parent worksheet object.

So either:

ThisWorkbook.Sheets("MySheet").Range("B" & licznik).Value

or

With ThisWorkbook.Sheets("MySheet")
    'some code...

    .Range("B" & licznik).Value

    'some more code...
End With

Upvotes: -1

Related Questions