Solved: Delete Empty Cells

[Giri]

Hi Guys,

I am trying to make a simple macro that deletes empty cells, but for some reeason the code below keeps skipping over any empty cells it encounters... Essentially, nothing is happening.. lol.

Any help with this would be greatly appreciated!

Kind Regards,

Giri

[vba]
Public Sub DeleteEmpty()

Dim row As Integer
Dim c
row = Range("D").End(xlDown).row

For Each c In Range("D1" & row)
c.Select

If IsEmpty(ActiveCell) = True Then
ActiveCell.Delete
End If

Next c
End Sub
[/vba]

[jolivanes]

Would this work?
[vba]
Range("D").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
[/vba]

[Giri]

Hi Jolivanes,

Thanks for the reply. I tried what you suggested but I received an error message:

"RunTime Error: 1004 No Cells were Found"

Thanks for your help.

Kind Regards,

Giri

[Bob Phillips]

[vba]Public Sub DeleteEmpty()

Dim lastrow As Long
Dim i As Long
lastrow = Range("D").End(xlDown).row

For i = lastrow to 1 step -1

If IsEmpty(Cells(i,"D").Value2) Then

Cells(i, "D").Delete Shift:=xlShiftUp
End If
Next i
End Sub [/vba]

[Giri]

Thanks for the replies xld and Jolivane.

Jolivane, I tried your suggestion on another worksheet and it was perfect! Not sure what was going on when I tried it before...

Kind Regards,

Giri

[jolivanes]

Glad you have it sorted.
Often cells appear to be empty but are not.