Friday, 8 August 2014

How To Delete Filtered Rows from a Range in Excel VBA

Excel VBA Macro to Delete Filtered Rows from a Range in a Sheet
Sub Del_Filter()
MyRange = ActiveSheet.UsedRange.Rows.Count
'MsgBox MyRange

ActiveSheet.Range("$A$1:$A$" & MyRange).AutoFilter Field:=1, Criteria1:="Invalid" _
        , Operator:=xlOr, Criteria2:="#NA"

With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(MyRange - 1).EntireRow.Delete
End With

'Removing the Auto Filter 
ActiveSheet.Range("A1").Select

        If ActiveSheet.AutoFilterMode Then Selection.AutoFilter

End Sub

Example :
Suppose We have the Data of Months in Column A , which contains two Invalid values known as "Invalid","#N/A" . If you want to Remove these two Invalid values from the below data , we can use the above Macro.


Month Name
Month Number
Quarter
Quarter Number
Aug
1
Q1
1
Sep
2
Q1
1
Oct
3
Q1
1
Nov
4
Q2
2
Dec
5
Q2
2
#NA
6
Q2
2
Feb
7
Q3
3
Mar
8
Q3
3
Invalid
9
Q3
3
May
10
Q4
4
Jun
11
Q4
4
Jul
12
Q4
4

Macro Result:
Month Name
Month Number
Quarter
Quarter Number
Aug
1
Q1
1
Sep
2
Q1
1
Oct
3
Q1
1
Nov
4
Q2
2
Dec
5
Q2
2
Feb
7
Q3
3
Mar
8
Q3
3
May
10
Q4
4
Jun
11
Q4
4
Jul
12
Q4
4



Thanks.,
TAMATAM
         [ BI-Analyst ]



1 comment:

  1. Hi My Dear Users Your Comments/Feed back/Queries is Important to Us...to Post More...........Always leave a Comment/Feedback on Every Visit of this Blog...

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts