VBA - automatically delete rows if they do not fall into categories chosen in 2 column filters

[sage123]

Hi guys,

I am having an issue getting some VBA to work for me. I copy and paste a sheet from one document into mine. My code formats the document slightly before then filtering from 2 columns and having any hidden rows removed.

Filtered columns are E ("BP10-AMB", "CY10-B/LINE", "GT10-B/LINE", "GT10-NF") and column f ("Overs")

Once filtered, I need the remaining rows deleted. I have tried using the below code but get 'Invalid or unqualified reference' and '.Parent.AutoFilter.Range.Offset(1).EntireRow.Delete' highlighted.

Any help would be, as always, appreciated!


Sub Overs_Sort()
'
' Overs_Sort Macro
'

'
Range("1:11,14:14").Select
Range("A14").Activate
Selection.Delete Shift:=xlUp
ActiveSheet.Shapes.Range(Array("Picture 2")).Select
Selection.Delete
Columns("A:I").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$444").AutoFilter Field:=5, Criteria1:=Array( _
"BP10-AMB", "CY10-B/LINE", "GT10-B/LINE", "GT10-NF"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$I$444").AutoFilter Field:=6, Criteria1:="OVER"
.Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
.Parent.AutoFilterMode = False

End Sub

 

[Yongle]

The easiest way to delete filtered rows is to use CurrentRegion as the range, and SpecialCells property to identify visible rows
- code below deletes all visible rows in the data below header row (row 1)
- offsetting by ONE row excludes header row (row 1) and includes the blank row immediately below the data
- SpecialCells throws an error if it returns nothing (and error handling is normally required)
- error handling is not required here because the blank row below the data is always visible even if the filter returns no other rows
(ie SpecialCells is guaranteed to return a minimum of one row)

    Dim rng As Range
[COLOR=#006400][I]'insert BELOW where filtering has occurs in your code[/I][/COLOR]
    Set rng = Range("A1").CurrentRegion.Offset(1)
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[sage123]

Hi Yongle,

Thank you for your reply. I tried your code however it ended up hiding all my rows of data. The list of data reaches row 178 in one example but upto row 441 was hidden. Do you have any suggestions why this would be the case?

Thank you!

 

[Yongle]

My code does not hide anything - it deletes all visible rows

To show the rows that were not deleted add these lines to the end of your code

    On Error Resume Next
    ActiveSheet.ShowAllData

 

[sage123]

Hi Yongle, ahh I see. I've just run it and all rows that were exempt from my filter criteria are now present with no hidden rows. Is there way to invert the process? I need my filters to follow the criteria set and then if there are any rows that do not comply for them to be deleted - rather than deleting the rows that contain data that match the filters set?

 

[Yongle]

I will post tested amended code when back at my PC tomorrow

 

[Yongle]

Test in a copy of your workbook
- VBA copies filtered data below original data rows before deleting original data rows
- to help with visibility whilst testing (and as a safety net!) sheet is copied BEFORE doing anything else

Paste code into SHEET module

Sub DeleteUnfilteredRows()
    Application.ScreenUpdating = False
        Me.Copy after:=Me      [COLOR=#006400][I] 'delete after testing - retains copy of original sheet[/I][/COLOR]
    Dim rng As Range:   Set rng = Range("A1").CurrentRegion
    rng.SpecialCells(xlCellTypeVisible).Copy Range("A1").Offset(rng.Rows.Count)
    On Error Resume Next
    Me.ShowAllData
    rng.EntireRow.Delete
    Range("A1").AutoFilter
        Me.Activate             [COLOR=#006400][I]'delete after testing[/I][/COLOR]
End Sub

 

[sage123]

Hi Yongle,

Thank you for getting back to me. I pasted your code into the 'Overs' sheet module however I get the message 'Compile Error: Invalid Use of Me keyword'. Do you know how to correct this?

Thank you!

 

[Yongle]

Are you sure you put the code in a sheet module?
- I think you put it in a standard module

Put this in the same module and run

Sub MyModuleName()
    MsgBox "Module " & Application.VBE.ActiveCodePane.CodeModule.Name
End Sub

If the code is in a sheet module
- message box returns "Module SheetXX"
If the code is in a standard module
- message box returns "Module ModuleXX"
If the code is in ThisWorkbook module
- message box returns "Module ThisWorkbook

Me is the worksheet if in a Sheet module
Me is the UserForm if in a UserForm module
Me is the workbook if in ThisWorkbook module
Me is not accepted in a standard module and the code fails as you describe