How to custom filter using a macro
Category: [Excel] Demo Available
How to custom filter using a macro
Requirement:
The user needs to write a macro that basically acts as a custom filter.
If column [dependent type] AND [dependent number] are empty AND [column index] is NOT equal to 1.
The user needs o delete that row from the data set.
The file the user is attaching is a smaller version of the original so the macro needs to be pretty quick.
The file is attached - please use the link below.
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149939-custom-filter-using-a-macro
Solution:
Code:
Dim lRow As Long
ActiveSheet.AutoFilterMode = False
lRow = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$S$" & lRow)
.AutoFilter Field:=19, Criteria1:="="
.AutoFilter Field:=18, Criteria1:="="
.AutoFilter Field:=17, Criteria1:="<>1"
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
An alternative method which may be faster if your actual data is large (100,000 rows +) as it does not use filtering.
Code:
Sub DeleteRows()
Dim x, i As Long
With Sheets("Sheet1").Cells(1).CurrentRegion '// CHANGE SHEET NAME TO SUIT
x = .Value
For i = 2 To UBound(x, 1)
If x(i, 17) <> 1 And x(i, 18) = "" And x(i, 19) = "" Then x(i, 17) = ""
Next
.Value = x
.Columns(17).SpecialCells(4).EntireRow.Delete
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by nyatiaju and KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy rows with filter based on values in another sheet |
| How to skip VBA Code if table filter returns nothing |
| How to use a macro to find value in a range of cells and combine values |
| How to create a macro for saving copy as csv with incremental file number |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.