mohdamir1989
New Member
- Joined
- Oct 17, 2017
- Messages
- 42
Hi All,
I have recorded a macro and need little help in refining it. Please see the code below
After the filter It executes this from beneath the header (A1) This is basically (A:F)
(Above line Leaving the header any row below basically to delete other than filter)
Basically I am first filtering data "Does not Begin With" 02 conditions then deleting the extra data and after this removing duplicates in all four sheets.
Help is required to refine the code as when I record it works fine but as the data refreshes (Auto importing data from multiples workbooks) results are not same.
Thanking in advance
I have recorded a macro and need little help in refining it. Please see the code below
After the filter It executes this from beneath the header (A1) This is basically (A:F)
VBA Code:
Range(Selection, Selection.End(xlToRight)).Select
VBA Code:
Range(Selection, Selection.End(xlDown)).Select
Basically I am first filtering data "Does not Begin With" 02 conditions then deleting the extra data and after this removing duplicates in all four sheets.
Help is required to refine the code as when I record it works fine but as the data refreshes (Auto importing data from multiples workbooks) results are not same.
VBA Code:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Sheets("First Time YN").Select
ActiveSheet.ListObjects("Worksheet__2").Range.AutoFilter Field:=6, Criteria1 _
:="<>Y*", Operator:=xlAnd, Criteria2:="<>N*"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlLeft).LineStyle = xlNone
Selection.Borders(xlRight).LineStyle = xlNone
Selection.Borders(xlTop).LineStyle = xlNone
Selection.Borders(xlBottom).LineStyle = xlNone
Selection.EntireRow.Delete
Range("Worksheet__2[[#Headers],[Column1]]").Select
ActiveSheet.ListObjects("Worksheet__2").Range.AutoFilter Field:=6
ActiveSheet.Range("Worksheet__2[#All]").RemoveDuplicates Columns:=2, Header _
:=xlYes
Sheets("Final-YN").Select
ActiveSheet.ListObjects("Worksheet").Range.AutoFilter Field:=6, Criteria1:= _
"<>Y*", Operator:=xlAnd, Criteria2:="<>N*"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("Worksheet[[#Headers],[Column1]]").Select
ActiveSheet.ListObjects("Worksheet").Range.AutoFilter Field:=6
ActiveSheet.Range("Worksheet[#All]").RemoveDuplicates Columns:=2, Header:= _
xlYes
Sheets("First Time 1-2").Select
ActiveSheet.ListObjects("Worksheet__3").Range.AutoFilter Field:=6, Criteria1 _
:="<>1*", Operator:=xlAnd, Criteria2:="<>2*"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("Worksheet__3[[#Headers],[Column1]]").Select
ActiveSheet.ListObjects("Worksheet__3").Range.AutoFilter Field:=6
ActiveSheet.Range("Worksheet__3[#All]").RemoveDuplicates Columns:=2, Header _
:=xlYes
Sheets("Final 1-2").Select
ActiveSheet.ListObjects("Worksheet__4").Range.AutoFilter Field:=6, Criteria1 _
:="<>1*", Operator:=xlAnd, Criteria2:="<>2*"
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("Worksheet__4[[#Headers],[Column1]]").Select
ActiveSheet.ListObjects("Worksheet__4").Range.AutoFilter Field:=6
ActiveSheet.Range("Worksheet__4[#All]").RemoveDuplicates Columns:=2, Header _
:=xlYes
Sheets("Sheet1").Select
Range("A1").Select
End Sub
Thanking in advance