Delete "Invisible" Rows

[BrittKnee]

Hi there. I am trying to do the opposite of deleting all visible rows after an autofilter. Basically, I want the user to be able to filter to a department and have the macro delete all of the not visible rows leaving only the data for their filtered department. I currently have the following code. It runs without error, but it doesn't delete the rows. Any help would be appreciated!

Option Explicit

Sub Dept_Only()

Dim oRow As Range, rng As Range
Dim myRows As Range
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    With ActiveSheet
        Set myRows = Intersect(Range("ReadyAll[Name]").EntireRow, .UsedRange)
        If myRows Is Nothing Then Exit Sub
    End With

    For Each oRow In myRows.Columns(2).Cells
        If oRow.EntireRow.Hidden Then
            If rng Is Nothing Then
                Set rng = oRow
            Else
                Set rng = Union(rng, oRow)
            End If
        End If
    Next
    
 Set ws = Sheets("Readiness_All")

    If rng.Rows.Count = 1 Then
   ws.Rows(rng.Row & ":" & rng.Row).Deletes
    ElseIf rng Is Nothing Then
   rng.EntireRow.Delete
End If
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[MARK858]

Is there any reason that you can't reverse the criteria on the Autofilter?

 

[BrittKnee]

Is there any reason that you can't reverse the criteria on the Autofilter?

There's 45 departments. I have a drop-down set up that filters to the specific department. I was wanting to have all of the invisible rows automatically deleted upon the selection.

 

[MARK858]

You set up in your code that the autofilter doesn't equal the cell reference with the dropdown, delete the visible rows then turn off (or .showalldata) the autofilter, all that remains is the cells with the value in the dropdown.

I don't see how the end result is any different to deleting the hidden rows when the autofilter does equal the cell with the dropdown.

 

[BrittKnee]

You set up in your code that the autofilter doesn't equal the cell reference with the dropdown, delete the visible rows then turn off (or .showalldata) the autofilter, all that remains is the cells with the value in the dropdown.

I don't see how the end result is any different to deleting the hidden rows when the autofilter does equal the cell with the dropdown.

I tried this and can't get the results. I looks like it filter, but it actually doesn't.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet

    Set ws = Sheets("Readiness_All")

    With Me
        If Not Intersect(Target, .Range("FilterValue")) Is Nothing Then
          If Target.Value <> "" Then
                .AutoFilterMode = False
                .Range("Readiness").AutoFilter Field:=7, Criteria1:="<>Target.Value"
        End If
      End If
        
    End With


End Sub

 

[MARK858]

Have a look at the file in the link below, K1 has the dropdown and filters on Column F (Sheet3 has a copy of the original data so that you can paste into Sheet1 if you want to retest).

Remember that you will probably have to unblock the file as you are downloading it from the internet.

BrittKnee.xlsm | Powered by Box

app.box.com

Which has the code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$K$1" Then
   
        If Target.Value <> "" Then
       
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
            End With
   
            With Range("A1:H" & Range("F" & Rows.Count).End(xlUp).Row)
   
                .AutoFilter 6, "<>" & Target.Value
           
                With .Offset(1).Resize(.Rows.Count - 1)
                    .EntireRow.Delete
                End With
           
                ActiveSheet.ShowAllData
   
            End With
   
            With Application
                .ScreenUpdating = True
                .EnableEvents = True
            End With
       
        End If
    End If
End Sub