VBA - Deleting determinated cells in Determinated Range

[frantheman97]

Hi guys,

I'm trying to delete cells that are different to "0" or "-".
But, I'm having the problem that the macro is deleting in all the worksheet, instead of the specific range.

Do you know how can I do to delete in a specific range?
I want to determine range ("b8,Aj861",.end(xldown)), but the last row must be the last value. and I dont know why it doesnt work.

Also, I would like not to delete the title or the filter row.

Sub prueba()

    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = ActiveSheet

   ' I want to determine this range, but the last row must be the last value.
   
   Set rng1 = ws.Range("b8:aj861")



    'Reset Existing Filters
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0
 
    'Apply Filter
    rng1.AutoFilter Field:=20, Criteria1:="<>-", _
        Operator:=xlAnd, Criteria2:="<>0"
      

    'Delete Rows
       
    Application.DisplayAlerts = False
    rng1.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    'Clear Filter
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0

End Sub

Thanks for your help.

 

[portews]

Just to clarify, you want to only have rows where column 20 (Field:=20) has either a zero or a dash in it?

Your code is worked for me to hide the zeros and dashes, delete the rest and show just those.

If you have one column that always has data in it, you can use this in place of the "Set rng1...":

LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = ws.Range("b8:aj" & LastRow)

I also added this just before the last "On Error Goto 0" to get rid of the column filters, if you want to.

If ws.AutoFilterMode Then ws.AutoFilterMode = False

 

[frantheman97]

Hi bud! Thanks for your help. It worked.

I really appreciate your help.