Friday, August 8, 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       

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog