Macro to delete rows running too fast

[rafael.cadina]

I created a macro to delete blank rows in my sheets. When i run it in debug mode via F8 (step-by-step) it works perfect. The thig is when i run it via F5 (play) some rows are not deleted in the process. Just below is my code. Does anybody can help me out? Thanks in advance

Sub Excluir_Linhas_Em_Branco() 'Exclui todas as linhas em branco
Coluna_Base = "B"
    Linha_Final = Cells(Rows.Count, Coluna_Base).End(xlUp).Row
    Dim rng As Range
    Set rng = Range(Coluna_Base & "2:" & Coluna_Base & Linha_Final)
        For Each c In rng.Cells
            If IsEmpty(c) = True Then
            c.EntireRow.Delete
            End If
        Next c
End Sub

 

[Andrew Poulsom]

To delete rows yu need to loop backwards, otherwise some may be missed.

For i = rng.Rows.Count To 1 Step -1
    If IsEmpty(rng.Cells(i, 1)) Then
        rng.Cells(i, 1).EntireRow.Delete
    End If
Next c

 

[baitmaster]

You are starting at the top, moving down, and then deleting rows. Every time you do this, everything below your current location shifts up a row, so when you move to the next row you jump past something and often miss it. You will see this in break mode too, if you watch carefully

Start at the bottom, and move up instead. Use variables to count the bottom row and use "for i = endRow to 1 step -1" instead of "for each ..."

 

[Jonmo1]

when deleting rows (or columns) you must go backwards from bottom to top.

So you can't use a for each c in range structure.

Try

Sub Excluir_Linhas_Em_Branco() 'Exclui todas as linhas em branco
Dim i As Long, Coluna_Base As String, Linha_Final As Long

Coluna_Base = "B"
Linha_Final = Cells(Rows.Count, Coluna_Base).End(xlUp).Row

For i = Linha_Final to 2 Step -1
    If Isempty(Cells(i, Coluna_Base)) Then
        rows(i).Entirerow.Delete
    End if
Next i
End Sub

 

[VoG]

With a loop to delete rows you have to loop backwards otherwise you will miss some rows.l However in this case there is no need to loop

Sub Excluir_Linhas_Em_Branco() 'Exclui todas as linhas em branco
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[texasalynn]

does this work?

Sub DeleteBlankRow()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LR To 2 Step -1
    If WorksheetFunction.CountA(Rows(i)) = 0 Then
        Rows(i).EntireRow.Delete
    End If
Next i
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[rafael.cadina]

Thanks very much guys.

I'm gonna try it later