CODEWITHSUNDEEP
excelvba
cherrun
cherrun

Reputation: 2142

Using VBA to check if below cell is empty

How do I use VBA in Excel to check if a below cell is empty or not? I want to sum all values in a specific range, but only, if the below cell is not empty.

Is that somehow possible with VBA or any other way?

Example:

4 2 3 2 1
2   3 1

Sum would be: 4 + 3 + 2 = 9.

Upvotes: 15

Views: 24229

Answers (4)

Sander de Jong
Sander de Jong

Reputation: 361

For those who are desperate: sometimes an Excel cell seems empty, but upon inspection may contain something invisible, such as a space character. And then all those IsEmpty and IsNull functions won't help you.

Upvotes: 0

LeasMaps
LeasMaps

Reputation: 302

I've had some problems using just 'IsEmpty' when the data is exported from other databases. This is the function I've developed:

Function IsVacant(TheVar As Variant) As Boolean
  'LeandraG 2010

  IsVacant = False

  If IsEmpty(TheVar) Then IsVacant = True
  If Trim(TheVar) = "" Then IsVacant = True
  If Trim(TheVar) = "'" Then IsVacant = True


End Function

Upvotes: 2

Nick
Nick

Reputation: 347

Try this simple code

If IsEmpty(ActiveCell.Offset(1, 0)) Then
'your code here
End If

Upvotes: 16

Siddharth Rout
Siddharth Rout

Reputation: 149277

I would recommend a Formula for this

FORMULA

=SUMPRODUCT((A1:E1)*(A2:E2<>""))

SNAPSHOT

enter image description here

If you still want VBA then

VBA

Option Explicit

Sub Sample()
    Dim rng As Range
    Dim Cl As Range
    Dim tot As Long

    Set rng = Range("A1:F1")

    For Each Cl In rng
        If Len(Trim(Cl.Offset(1))) <> 0 Then tot = tot + Cl.Value
    Next

    Debug.Print tot
End Sub

In fact you can have many versions in VBA. You can evaluate the above formula as well. For example

Option Explicit

Sub Sample()
    Debug.Print Evaluate("=SUMPRODUCT((A1:E1)*(A2:E2<>""""))")
End Sub

Upvotes: 3

Related Questions