Have to run loop mulitple times to work

[dshafique]

Hey guys, i have a loop in my macros which goes through a range and deletes any cells that contain "(blank)".
the problem is, i have to step through the loop a few times in vba for it to delete all the instances. otherwise some cells with "(blank") still remain.

Range("date").Select
    For Each cl In Range("date")
    If cl.Formula = "(blank)" Then
    cl.Delete Shift:=xlUp
    End If
Next cl

 

[Rick Rothstein]

Hey guys, i have a loop in my macros which goes through a range and deletes any cells that contain "(blank)".
the problem is, i have to step through the loop a few times in vba for it to delete all the instances. otherwise some cells with "(blank") still remain.

Range("date").Select
    For Each cl In Range("date")
    If cl.Formula = "(blank)" Then
    cl.Delete Shift:=xlUp
    End If
Next cl

If the blanks in the "date" range are true blanks (that is, not formulas displaying ""), then you can do it with one line of code...

[table="width: 500"]
[tr]
	[td]Intersect(ActiveSheet.UsedRange, Range("date").SpecialCells(xlBlanks).EntireRow).Delete[/td]
[/tr]
[/table]

If there is a possibility that the range will not contain any blanks, then you need to protect the code against the error that would be generated for that situation...

[table="width: 500"]
[tr]
	[td]On Error Resume Next
Intersect(ActiveSheet.UsedRange, Range("date").SpecialCells(xlBlanks).EntireRow).Delete
On Error GoTo 0[/td]
[/tr]
[/table]

 

[Fluff]

Hello Rick
According to the OP in post#6

the cell literally says "(blank)" parenthesis included

 

[Rick Rothstein]

Hello Rick
According to the OP in post#6

Missed that... thanks.

In that case, still assuming no formula in the cells of the "date" defined name range, it will take two lines of code...

[table="width: 500"]
[tr]
	[td]Range("date").Replace "(blank)", "", xlWhole, , False, , False, False
Intersect(ActiveSheet.UsedRange, Range("date").SpecialCells(xlBlanks).EntireRow).Delete[/td]
[/tr]
[/table]

And, again, if there might not be any cells marked "(blank)", this...

[table="width: 500"]
[tr]
	[td]On Error Resume Next
Range("date").Replace "(blank)", "", xlWhole, , False, , False, False
Intersect(ActiveSheet.UsedRange, Range("date").SpecialCells(xlBlanks).EntireRow).Delete
On Error GoTo 0[/td]
[/tr]
[/table]