VBA Macro help required

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)
VBA Code:
Range(Selection, Selection.End(xlToRight)).Select
VBA Code:
Range(Selection, Selection.End(xlDown)).Select
(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.

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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,223,992
Messages
6,175,822
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top