PDA

View Full Version : Solved: Delete Empty Cells



Giri
07-06-2011, 09:36 PM
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


Public Sub DeleteEmpty()

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

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

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

Next c
End Sub

jolivanes
07-06-2011, 10:24 PM
Would this work?

Range("D:D").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

Giri
07-06-2011, 10:31 PM
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
07-06-2011, 11:39 PM
Public Sub DeleteEmpty()

Dim lastrow As Long
Dim i As Long
lastrow = Range("D: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

Giri
07-07-2011, 03:27 AM
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
07-07-2011, 01:23 PM
Glad you have it sorted.
Often cells appear to be empty but are not.