How to check if a cell is empty?

Question

Looking for coding that checks if cell is empty or not. If it is not empty then move to the next cell group. But I need to check if the next cell group is empty or not. If not, then move to the next and so on.

My Current coding below.

If IsEmpty(ActiveSheet.Range("h3")) Then
    Do
    Checkbox1.Value = True
    Range("H3") = 17002
    Sheets("Sheet1").Range("I3") = Printerformat2.Text
Else
    Checkbox1.Value = True
    Range("l3") = 17002
    Sheets("Sheet1").Range("m3") = Printerformat2.Text
End If

Might the cells have formulas? – Tim Williams Aug 12 '11 at 16:24 — Tim Williams, Aug 12 '11 at 16:24
That code can't possibly compile. `Do` without `Loop`. – Jean-François Corbett Aug 15 '11 at 08:13 — Jean-François Corbett, Aug 15 '11 at 08:13

score 1 · Answer 1 · answered Aug 13 '11 at 01:23

1

You need to use a for loop to iterate through the specified range in "H"

Dim i As Long
With ActiveSheet
  For i = 1 to 500

    If IsEmpty(.Range("h" & CStr(i)).Value) Then
     'Do 'Not sure where you're going with this one? This is not really needed from what I can tell.
     Checkbox1.Value = True
     .Range("H" & CStr(i)).Value = 17002
     Sheets("Sheet1").Range("I" & CStr(i)).Value = Printerformat2.Text
    Else
      Checkbox1.Value = True
      .Range("l" & CStr(i)) = 17002
      Sheets("Sheet1").Range("m" & CStr(i)).value = Printerformat2.Text
    End If

   Next
End With

Hope that helps?

answered Aug 13 '11 at 01:23

Allen Gammel

396
2
4

2

+1 but you're doing yourself a disservice by using string concatenation to build cell addresses. – Jean-François Corbett Aug 15 '11 at 08:14
What method would you suggest instead? I normally use "Cells" like so: `Private Enum Columns Employee = 1 Department = 2 Manager = 3 End Enum Private Function getCellByString(column As Columns, row As Long) As String With Sheet1 getCellByString = CStr(.Cells(row, column)) End With End Function Public Sub GetDetails() MsgBox getCellByString(Manager, 2) End Sub ` – Allen Gammel Aug 15 '11 at 13:03

score 1 · Answer 2 · answered Aug 13 '11 at 18:15

1

I think you should have a look at Range("your range").specialcells(xlCellTypeBlanks). This is the fastest way to loop through empty cells.
If you need to loop on non blank cells, you can check if your cell Intersect the empty cells range.

answered Aug 13 '11 at 18:15

iDevlop

24,841
11
90
149

score 0 · Answer 3 · answered Aug 03 '12 at 09:43

0

Use

 Application.WorksheetFunction.isblank(ActiveSheet.range("h3"))

instead of

 IsEmpty(ActiveSheet.Range("h3"))

Cordially

answered Aug 03 '12 at 09:43

MUY Belgium

2,330
4
30
46

score 0 · Answer 4 · answered Aug 13 '11 at 13:46

0

The most optimized way to make sure a cell is not empty is "If Len(cell) <> 0".

You can use .offset to access another cell in relation to it's position from the current cell, or reference it directly, so you can check if it's empty or not.

answered Aug 13 '11 at 13:46

Gaijinhunter

14,587
4
51
57

How to check if a cell is empty?

4 Answers4

Linked