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

 

[Fluff]

How about

Sub DelClls()

    Dim Cnt As Long
    Dim Rng As Range
    
    Set Rng = Range("date")
        For Cnt = 1 To Rng.Cells.Count
            If Rng.Cells(Cnt).Value = "" Then Rng.Cells(Cnt).Delete shift:=xlUp
        Next Cnt
End Sub

 

[Joe4]

Since deleting rows move all the rows below it up one, if you are going to loop through your range, you need to do it in reverse order so you don't miss any rows.
However, I don't believe you can control the order in a FOR EACH loop. So you probably need to change how you are doing it.

One way is to use the row numbers, and use a FOR/NEXT loop.
See: https://www.mrexcel.com/forum/excel-questions/973807-vba-delete-rows-named-range-if-value-false.html

Other ways include using Filters along with VBA to delete rows. You can do some Google searches to find examples of those.

 

[Fluff]

Since deleting rows move all the rows below it up one, if you are going to loop through your range, you need to do it in reverse order so you don't miss any rows.

Quite right Joe4, changed the for each to for next, for that reason & stupidly forgot to run it in reverse.
Code should be

Sub DelClls()

    Dim Cnt As Long
    Dim Rng As Range
    
    Set Rng = Range("date")
        For Cnt = Rng.Cells.Count To 1 Step -1
            If Rng.Cells(Cnt).Value = "" Then Rng.Cells(Cnt).Delete shift:=xlUp
        Next Cnt
End Sub

 

[Joe4]

Fluff,

I could be wrong, but the way I read the problem, I think they mean the entry in the cell is actually the phrase "(blank)", and not a literal blank.
In that case, you would just need to update your code to:

If Rng.Cells(Cnt).Value = "[COLOR=#ff0000](blank)[/COLOR]" Then Rng.Cells(Cnt).Delete shift:=xlUp

 

[dshafique]

@Fluff it still doesnt delete all of them. and its not a blank cell, the cell literally says "(blank)" parenthesis included. it still shows 3 cells at the end with blank in them.

 

[Joe4]

Fluff's solution with my amendment should do what you want.

Sub DelClls()


    Dim Cnt As Long
    Dim Rng As Range
    
    Set Rng = Range("date")
        For Cnt = Rng.Cells.Count To 1 Step -1
            If Rng.Cells(Cnt).Value = "(blank)" Then Rng.Cells(Cnt).Delete shift:=xlUp
        Next Cnt
End Sub

 

[Fluff]

Try this

Sub DelClls()

    Dim Cnt As Long
    Dim Rng As Range
    
    Set Rng = Range("date")
        For Cnt = Rng.Cells.Count To 1 Step -1
            If Rng.Cells(Cnt).Value = """(blank)""" Then Rng.Cells(Cnt).Delete shift:=xlUp
        Next Cnt
End Sub

If this doesn't work, it might be worth checking that the remaining "Blanks" don't have some spaces

 

[dshafique]

thank you both, now it works. at first i was having trouble because I already had a rng defined as range above, so i just changed this to like rng1. thanks guys!

 

[Fluff]

Glad we could help & thanks for the feedback