Delete unwanted VBA values

Question

Delete unwanted VBA values

Navigation

#1 by (0 votes)

1

Good morning guys, I have the following macro to run a filter inside a column and bring me a certain value. but in that same column there are other values, I would like to delete the values that I will not use, how can I do it? follow the code.

Set ws3 = ActiveWorkbook.Worksheets("Planilha3")
With ws3
    'Limpa os Autofiltros da Planilha para evitar erros
    If .FilterMode Then
        .ShowAllData
    End If
    'Última Linhada colunaE
    UltimaLinhaE = .Cells(.Rows.Count, "E").End(xlUp).Row
    'Autofiltro
    .Range("E1:E" & UltimaLinhaE).AutoFilter Field:=5, Criteria1:="Cell 01"
End With

vba

asked by anonymous 23.05.2018 / 15:43

1 answer

How do I specify a remote address, within several others, using an input type="checkbox" 2 simple columns with bootstrap

score 0 · Accepted Answer

Code

Dim ws3 As Worksheet
Dim UltimaLinhaE As Long
Dim RangeVisivel As Range
Set ws3 = ActiveWorkbook.Worksheets("Planilha3")
With ws3
    'Limpa os Autofiltros da Planilha para evitar erros
    If .FilterMode Then
        .ShowAllData
    End If
    'Última Linhada colunaE
    UltimaLinhaE = .Cells(.Rows.Count, "E").End(xlUp).Row
    'Autofiltro
    .Range("E1:E" & UltimaLinhaE).AutoFilter Field:=1, Criteria1:="<>Cell 01"
    On Error Resume Next
    Set RangeVisivel = .Range(.Cells(1, "E"), .Cells(UltimaLinhaE, "E")).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not RangeVisivel Is Nothing Then
        RangeVisivel.EntireRow.Delete
    End If
End With

Explanation

First the different values of the desired one are filtered: .Range("E1:E" & UltimaLinhaE).AutoFilter Field:=1, Criteria1:="<>Cell 01"

Then a range is defined with the values of the visible cells: Set RangeVisivel = .Range(.Cells(1, "E"), .Cells(UltimaLinhaE, "E")).SpecialCells(xlCellTypeVisible)

If this range exists, the entire line is deleted: RangeVisivel.EntireRow.Delete