PDA

View Full Version : [SOLVED] Deleting rows on filter



Hudson
10-25-2016, 07:01 AM
Hi all,

I was trying to delete all unnecessary lines from my given sheet . using filter . to be more precise I have three columns and could be varies . (Company /Code Customer/Customer name) now I want to put filter on customer name and filter with two account numbers (10024827 , 10025383) and I want remaining to be deleted with all the three columns .



attached is the example for your reference .

YasserKhalil
10-25-2016, 09:13 AM
May be

Sub Test()
Dim R As Range, Rng As Range, myRange As Range
With ActiveSheet
.Range("A1:C1").AutoFilter Field:=2, Criteria1:=Array("10024827", "10025383"), Operator:=xlFilterValues
Set myRange = Intersect(.Range("A:A").EntireRow, .UsedRange)
If myRange Is Nothing Then Exit Sub
For Each R In myRange.Columns(1).Cells
If R.EntireRow.Hidden Then
If Rng Is Nothing Then
Set Rng = R
Else
Set Rng = Union(Rng, R)
End If
End If
Next R
If Not Rng Is Nothing Then Rng.EntireRow.Delete
.Range("A1:C1").AutoFilter
End With
End Sub

onlyadrafter
10-25-2016, 09:51 AM
Hello,

no need for looping through data


Sub Macro2()
Range("A1:C1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$" & Range("C" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:= _
"<>10024827", Operator:=xlAnd, Criteria2:="<>10025383"
Rows("2:" & Rows.Count).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$C$3").AutoFilter Field:=2
End Sub

Hudson
10-26-2016, 02:06 PM
Excellent both the codes are working fine.. thanks guys .. your help mean a lot to me... :):clap:

YasserKhalil
10-26-2016, 03:37 PM
You're welcome.
Glad I can offer some help for you