Using VBA to check if below cell is empty

Question

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.

score 16 · Answer 1 · answered Apr 22 '12 at 17:52

16

Try this simple code

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

answered Apr 22 '12 at 17:52

Nick

347
5
11

Siddharth Rout · Accepted Answer · 2012-04-21T22:51:59.807

3

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

edited Apr 21 '12 at 22:51

answered Apr 21 '12 at 22:45

Siddharth Rout

147,039
17
206
250

@Siddarth Rout, very nice approach. – Marc Apr 21 '12 at 23:06
1

@Cherrun: "<>" stands for `Not Equal To`. So I am simply taking cells into account which are not empty in the range A2:E2. – Siddharth Rout Apr 22 '12 at 08:13
1

@Siddharth Rout I agree, clever use of SumProduct but I think you have misunderstood cherrun's question about "<>". `A2:E2<>""` returns True or False for each column. You are then using `True` or `False` as an integer. Excel treats True as 1 and False as 0. So the SumProduct is `4*True + 2*False + 3*True + 2*True + 1*False` = `4*1 + 2*0 + 3*1 + 2*1 + 1*0` = `9`. Clever but, unless you know that booleans are implicitly converted to integers, incomprehensible. – Tony Dallimore Apr 22 '12 at 10:45
@TonyDallimore: Ah! In that case I misunderstood the question :) – Siddharth Rout Apr 22 '12 at 11:01
@Siddharth Rout. I think your answers are beautifully presented, you display a depth of knowledge I will never aspire to and where do you find the time to answer so many questions but sometimes I suspect you forget how far ahead of the rest of us you are. However, even if I am correct about cherrun's true question, keep up the good work; your answers are a pleasure to read. – Tony Dallimore Apr 22 '12 at 11:17
@Tony Dallimore and Sidd Thank you both for your answers! In fact I didn't know that <> meant not equal, and therefore didn't know that it was converted to integers as well. Thanks you both! – cherrun Apr 22 '12 at 13:49
+1 Another good answer. Agree with @TonyDallimore. Your answers are a pleasure to read. Keep up the good work. :) – Pradeep Kumar Apr 22 '12 at 14:01

score 2 · Answer 3 · answered Jan 14 '14 at 02:19

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

score 0 · Answer 4 · answered Feb 15 '22 at 13:58

0

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.

answered Feb 15 '22 at 13:58

Sander de Jong

351
6
18

Using VBA to check if below cell is empty

4 Answers4

Linked

Related