Macro to exclude filtered table row

Question

I created a table with data from an account balance and I am doing a macro to select only a few values. An assignment of the macro would be to filter in one of the columns all zeroed values so as to exclude the rows of these accounts that had no movement.

After performing the filter, I try to delete only the filtered lines from the table, but I cannot.

Someone would know me if there’s a way to do it?

Answer 1

One option would be to apply the algorithm below. The worksheet does not need to be filtered for the functioning of it.

The Sub little erasers receives a column as parameter and, from line 2, traverses the cells that have value = 0 in the current line and column passed as parameter. If the value is = 0, then the current line is deleted.

Sub apagaLinhas(coluna As Integer)
    Dim sheet As Worksheet
    Dim linha As Long

    Set sheet = Worksheets("Plan1")
    linha = 2

    While (sheet.Cells(linha, coluna).Value <> "")
        If (sheet.Cells(linha, coluna).Value = 0) Then
            sheet.Rows(linha).Delete Shift:=xlUp
        Else
            linha = linha + 1
        End If
    Wend
End Sub

Use the function below to test.

Sub teste()
    apagaLinhas (2)
End Sub