Delete Empty Cells With VBA

Hello friend,

I am a little confused as to whether the cells contain the quotation marks or if they are blank and you used quotation marks to illustrate they are blank. In response to if the cell has a value of zero here is a loop which will work for you.

Option Explicit

Sub Deletevalue()
Dim iCell As Range

For Each iCell In Range("A1:D100")
    If iCell = 0 Then
        iCell.Delete
    End If
Next iCell

End Sub

Note: You will want to adjust the range to meet your needs. Also, since i wasn't sure what the range was this may not be the best way to do what you want to do. For example if the range is really big this macro will be really slow. Let me know.

Hi there

Thanks for the code, i realised what i needed to do & adapted it to suit. I have included the code below to help anyone who might see it & need similar.

Cheers
Mark

Sub Deletevalue()
Dim iCell As Range

For Each iCell In Range("A5:A32")
    If iCell = 0 Then
        iCell.ClearContents
    End If
Next iCell

End Sub

Just wanted to update a better way for future readers now that I see what you want to do

Try:

Option Explicit

Sub Deletevalue()
Dim lastrow As Long
Dim iCell As Long

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For iCell = 3 To lastrow
    If Range("A" & iCell) = 0 Then
        Range("A" & iCell).ClearContents
    End If
Next iCell

End Sub

This way we only focus on column A rather than all the cells. Much faster