Check for Not Empty Cells

[Ivy_1011]

Hi,

I have the code below which works well. It checks to see if Cell "A1" is not empty if it is not empty then it runs the code only in the cells are blank in column A.

I would like to change the code so that it automatically checks each cell in column A, if has data then it runs the code. This code would add "0" to the empty cells.

Sub Empty_Cells()
'
Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range
Dim myCol As Range





Set myRange = Worksheets("Data").Range("A4:H12")



'I would like to change this line of code to a loop in column ("A5:A50") to check if the cell is Not Empty.

If Not IsEmpty(Range("A1").Value) = True Then



For Each myCell In myRange
c = c + 1
If IsEmpty(myCell) Then

myCell = 0
'myCell.Interior.Color = RGB(255, 87, 87)
i = i + 1
End If
Next myCell


MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."
'

End If
'
End Sub

 

[James006]

Hi,
What is exactly your obstacle ...?

Sub Empty_Cells()
Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range
Dim myCol As Range

Set myRange = Worksheets("Data").Range("A5:A50")
'I would like to change this line of code to a loop in column ("A5:A50") to check if the cell is Not Empty.

    If Not IsEmpty(Range("A1").Value) = True Then
        For Each myCell In myRange
            c = c + 1
            If IsEmpty(myCell) Then
                myCell = 0
                'myCell.Interior.Color = RGB(255, 87, 87)
                i = i + 1
            End If
        Next myCell
            MsgBox _
            "There are total " & i & " empty cell(s) out of " & c & "."
    End If
End Sub

 

[Ivy_1011]

Hi,
What is exactly your obstacle ...?

Sub Empty_Cells()
Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range
Dim myCol As Range

Set myRange = Worksheets("Data").Range("A5:A50")
'I would like to change this line of code to a loop in column ("A5:A50") to check if the cell is Not Empty.

    If Not IsEmpty(Range("A1").Value) = True Then
        For Each myCell In myRange
            c = c + 1
            If IsEmpty(myCell) Then
                myCell = 0
                'myCell.Interior.Color = RGB(255, 87, 87)
                i = i + 1
            End If
        Next myCell
            MsgBox _
            "There are total " & i & " empty cell(s) out of " & c & "."
    End If
End Sub

I have a spreadsheet which I attached the image for. I want to run a code that adds zero to empty cells, (this part of the code I have), based on the condition that this only applies to the rows that have data in Column A, and the row is a variable. I want it to loop through A1... then apply the code, then A2 then apply the code, and so on.

 

[Ivy_1011]

I want to run a code that adds zero to empty cells on a table, (this part of the code I have), based on the condition that this only applies to the rows that have data in Column A but one row at a time. I want it to loop through A1... then apply the code, then A2 then apply the code, and so on.

 

[James006]

Not sure to fully understand ...

To replace Blanks by 0, one line instruction is enough
Adjust YourRange to your actual Range :

YourRange.Replace What:="", Replacement:="0"

 

[Ivy_1011]

Not sure to fully understand ...

To replace Blanks by 0, one line instruction is enough
Adjust YourRange to your actual Range :

YourRange.Replace What:="", Replacement:="0"

It should have a condition that column A of the row is not a blank cell, only if there is data in column A add a zero to the empty cells of the row.

 

[Joe4]

I think your range explanation may be a bit confusing.

It may be helpful to show us some example data and then your expected output. Images often clear up any confusion.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[James006]

Hi,
Have you tried to use Filter on Column A to work with your Target Range ...

 

[Ivy_1011]

Hi,
Have you tried to use Filter on Column A to work with your Target Range ...

I have not. My objective is to add zeros to blank cells within the table Range A4:H12 only if column A has data in that row, if column A is empty then the cells remain empty.

 

[James006]

Hi,
You could test

Sub TestBlanks()
Dim tbl As ListObject
Dim rng As Range
    'Change the Name of your Table and your Worksheet
    Set tbl = Worksheets("Sheet1").ListObjects("Table1")
    ' Filter to retain records Not Empty in Column A
    tbl.Range.AutoFilter Field:=1, Criteria1:="<>"
    'Define Range with Visible Cells Only
    Set rng = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    ' Replace Blanks by Zero
    rng.Replace What:="", Replacement:="0"
End Sub