Avatar of Edward Pamias
Edward Pamias
Flag for United States of America asked on

I need Excel macro modified to filter remote2office and filter out duplicates.

I need a macro modified to filter out(needs to be added)  remote2office and filter out duplicates in column B, title of the row with the possible duplicates is "User Reference Number"

Sub CopyFilteredRow2()
Dim SrcWs As Worksheet, NewSh As Worksheet
Dim SrcLR As Long
Dim j As Long
Dim Crit(2) As String
Dim StrIF As String
Dim RngFilter As Range
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
Set SrcWs = ActiveSheet
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row
Crit(0) = "*Asset Verification*"
Crit(1) = "*R2O*"
Crit(2) = "*Project*"
With SrcWs
    .AutoFilterMode = False
    .Range("L1").Value = "Temp"
    For j = 2 To SrcLR
        StrIF = "=if(or(isnumber(search(" & Chr(34) & Crit(0) & Chr(34) & ",h" & j & ")),isnumber(search(" & Chr(34) & Crit(1) & Chr(34) _
            & ",h" & j & ")),isnumber(search(" & Chr(34) & Crit(2) & Chr(34) & ",h" & j & "))),999,0)"
        .Range("l" & j).Formula = StrIF
    Next j
End With
Set RngFilter = SrcWs.Range("A1:L" & SrcLR)
With RngFilter
    .AutoFilter Field:=12, Criteria1:="0", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy
    Worksheets.Add.Paste
End With
SrcWs.Columns(12).Delete
ActiveSheet.Name = "FilteredSheet"
Set NewSh = Worksheets("FilteredSheet")
Application.CutCopyMode = False
If SrcWs.AutoFilterMode = True Then SrcWs.AutoFilterMode = False
NewSh.Activate
NewSh.Columns(12).Delete
NewSh.Columns.AutoFit
NewSh.Range("A2").Select
ActiveWindow.FreezePanes = True
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Microsoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
Shums Faruk

Hi Edward,

Please try below:
Sub CopyFilteredRow()
Dim SrcWs As Worksheet, NewSh As Worksheet
Dim SrcLR As Long, FshLR As Long
Dim j As Long, i As Long
Dim Crit(3) As String
Dim StrIF As String
Dim RngFilter As Range
Dim xWs As Worksheet
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
'Delete Sheets if it exists
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name = "FilteredSheet" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True
Set SrcWs = Worksheets("Data")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row
Crit(0) = "*Asset Verification*"
Crit(1) = "*R2O*"
Crit(2) = "*Project*"
Crit(3) = "*remote2office*"
With SrcWs
    .AutoFilterMode = False
    .Range("T1").Value = "Temp"
    For j = 2 To SrcLR
        StrIF = "=if(or(isnumber(search(" & Chr(34) & Crit(0) & Chr(34) & ",f" & j & ")),isnumber(search(" & Chr(34) & Crit(1) & Chr(34) _
            & ",f" & j & ")),isnumber(search(" & Chr(34) & Crit(2) & Chr(34) & ",f" & j & ")),isnumber(search(" & Chr(34) & Crit(3) & Chr(34) & ",f" & j & "))),999,0)"
        .Range("l" & j).Formula = StrIF
    Next j
End With

Set RngFilter = SrcWs.Range("A1:S" & SrcLR)
With RngFilter
    .AutoFilter Field:=12, Criteria1:="0", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy
    Worksheets.Add.Paste
End With
SrcWs.Columns(12).Delete
ActiveSheet.Name = "FilteredSheet"
Set NewSh = Worksheets("FilteredSheet")
Application.CutCopyMode = False
If SrcWs.AutoFilterMode = True Then SrcWs.AutoFilterMode = False
NewSh.Activate
NewSh.Columns(12).Delete
FshLR = NewSh.Range("B" & Rows.Count).End(xlUp).Row
For i = FshLR To 1 Step -1
    If Application.CountIf(NewSh.Columns(2), NewSh.Cells(i, 2)) > 1 Then 
        NewSh.Rows(i).Delete
    End If
Next i
NewSh.Columns.AutoFit
NewSh.Range("A2").Select
ActiveWindow.FreezePanes = True
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Shums Faruk

Sorry Edward, please change this line:
Set RngFilter = SrcWs.Range("A1:S" & SrcLR)
to
Set RngFilter = SrcWs.Range("A1:L" & SrcLR)
ASKER CERTIFIED SOLUTION
Shums Faruk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Edward Pamias

ASKER
This update worked perfectly. Thank you!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Shums Faruk

You're Welcome Edward! Pleased to help.