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.
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.