CODEWITHSUNDEEP
vbaexcel
DL1
DL1

Reputation: 201

VBA check for empty cells in column and print value

In column J there are empty rows and rows with a value such as checked.

I have tried to write VBA code that prints "unchecked" where there is an empty cell AND this works, but when it hits a cell with a value (checked) it stops. And it won't go down to the next cell probably because I have formulas in the cell that prints nothing if not fullfilled, but it still contains that formula. In my case I have empty cells until J7 and then it starts again at J15. But this can change from time to time regarding source data.

The reason I want to do it like this is because I have a formula in column J that already have printed some values and then some VBA code that checks for other values in a different column and prints to column J. Column J is the filter master column sort of. So this is the way I have to do it I guess.

My code right now is,

Sub DoIfNotEmpty()
    Dim ra As Range, re As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Set ra = .Range("J:j25")
        For Each re In ra
            If IsEmpty(re.Value) Then
                re.Value = "unchecked"
            End If
        Next re
    End With
End Sub

Can I print to empty cells if the cell contains a formula which in this case has an if statement that is not filled?

Upvotes: 1

Views: 14379

Answers (2)

user3598756
user3598756

Reputation: 29421

You could exploit the Specialcells() method of Range object:

Sub DoIfNotEmpty()
    ThisWorkbook.Worksheets("Sheet1").Range("J1:J25").SpecialCells(xlCellTypeBlanks).Value = "unchecked"
End Sub

Or, if you have formulas returning blanks, then AutoFilter() "blank" cells and write in them

Sub DoIfNotEmpty()
    With ThisWorkbook.Worksheets("Sheeet1").Range("J1:J25") '<--| reference your range (first row must be a "header")
        .AutoFilter Field:=1, Criteria1:="" '<--| filter its empty cells
        If Application.WorksheetFunction.Subtotal(103, .cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Value = "unchecked" '<--| if any cell filtered other than headers then write "unchecked" in them
        .Parent.AutoFilterMode = False
    End With
End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57743

Except from @Maxime Porté's points out that it should be .Range("J1:j25"). I guess the cells only look empty, but they are not.

A cell that contains an empty string, "", is not empty anymore, but it looks like it. You can test it like this:

  1. In a new worksheet write in A1: ="" (there is no space in between!)
  2. Copy A1 and special paste values in A1. A1 now looks to be empty.
  3. Run Debug.Print IsEmpty(Range("A1").Value) in VBA and you get a FALSE.

The cell A1 is not empty any more, because it contains an empty string.

What can you do?

Sub DoIfNotEmpty()
    Dim ra As Range, re As Range

    With ThisWorkbook.Worksheets("Sheet1")
    Set ra = .Range("J1:J25")
        For Each re In ra
            If IsEmpty(re.Value) or re.Value = vbNullString Then
                re.Value = "unchecked"
            End If
        Next re
    End With
End Sub

This will mark pseudo empty cells as "unchecked" too. But be aware that it also kills formulas that result in an empty string, "".

Upvotes: 3

Related Questions