Delete Filtered Data

[erock24]

I'm trying to have a macro filter a range then delete the results if there are any. I have successfully created the code to filter the range, but I am unable to correctly delete the results. I'm trying to delete only the visible cells in that range, but the macro for some reason seems to be deleting the entire row even though i'm not telling it too. What's wrong??

Note: I posted this here a few days ago, with no luck: http://www.mrexcel.com/forum/showthread.php?t=454229

Private Sub DeleteZeroRows(ByVal wsheet As Worksheet)
  Dim stripArrVar As Variant
  Dim xArr As Variant
  Dim firstRow As Long
  Dim filterRng As Range
  
  With wsheet
    firstRow = .Range("HeaderRow").Row
    stripArrVar = Array("AllA", "AllD", "AllH")
    For Each xArr In stripArrVar
        .AutoFilterMode = False
        Set filterRng = .Range(.Cells(firstRow, .Range(xArr).Column - 4), .Cells(firstRow, .Range(xArr).Column + 2))
        filterRng.AutoFilter Field:=5, Criteria1:=0
        .Range(.Cells(firstRow + 1, .Range(xArr).Column - 4), .Cells(Rows.Count, .Range(xArr).Column + 2)).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    Next xArr
    .AutoFilterMode = False
  End With
End Sub

[Leith Ross]

Hello erock24,

In your code you are extending the range to the bottom of the worksheet by using Rows.Count. Because the SpecialCells property has a limit of 8192 entires, you will need to limit the range to the last entry in the search range. Once this limit is exceeded, things get weird.

[erock24]

Leith,

Thank you for the reply. I edited the code to only go to the bottom of the filtered data. I get the same result. The code seems to be deleting the entire row, when I only want cells within my designated range to be deleted. Is this possible or does the code always default to entire rows in filter mode??

Private Sub DeleteZeroRows(ByVal wsheet As Worksheet)
  Dim stripArrVar As Variant
  Dim xArr As Variant
  Dim firstRow As Long
  Dim filterRng As Range
  Dim lstfiltrow As Long
  
  With wsheet
    firstRow = .Range("HeaderRow").Row
    stripArrVar = Array("AllA", "AllD", "AllH")
    For Each xArr In stripArrVar
        .AutoFilterMode = False
        Set filterRng = .Range(.Cells(firstRow, .Range(xArr).Column - 4), .Cells(firstRow, .Range(xArr).Column + 2))
        filterRng.AutoFilter Field:=5, Criteria1:=0
        lstfiltrow = Cells(Rows.Count, .Range(xArr).Column).End(xlUp).Row
        If lstfiltrow > firstRow Then
            .Range(.Cells(firstRow + 1, .Range(xArr).Column - 4), .Cells(lstfiltrow, .Range(xArr).Column + 2)).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
        End If
    Next xArr
    .AutoFilterMode = False
  End With
End Sub

[Leith Ross]

Hello erock24,

Can you post your workbook for review?

[erock24]

Sorry for the late response....attached is a sample with the filter code. The filtering works great. The code will filter the blue range 1st, then yellow, then green. It will not find anything in blue or yellow, but it will in green. But then the code doesn't work right. Instead of deleting just the green filtered range, it seems to delete entire rows.

[Leith Ross]

Hello erock24,

As I understand your code, it is supposed to delete only the rows within each group if there is a zero in the fifth column. If that is correct then this macro will do that.

Sub DeleteZeroRows()

  Dim I As Long
  Dim Rng As Range
  Dim Rgn As Variant
    
   'Array holds the first cell address of each market group's range
    For Each Rgn In Array("B4", "J4", "R4")
    
     'Get the full cell range for the market group
      Set Rng = Range(Rgn).CurrentRegion
      
     'Skip the headers
      Set Rng = Rng.Offset(4, 0).Resize(RowSize:=Rng.Rows.Count - 4)
      
       'Check the values in column 5 of each market group's range
        For I = Rng.Rows.Count To 1 Step -1
         'Delete only the row within the market group's range if = 0 (column 5)
          If Rng.Item(I, 5) = 0 Then Rng.Rows(I).Delete Shift:=xlShiftUp
        Next I
        
    Next Rgn
    
End Sub