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

Edward Pamias
Edward Pamias used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Sorry Edward, please change this line:
Set RngFilter = SrcWs.Range("A1:S" & SrcLR)
to
Set RngFilter = SrcWs.Range("A1:L" & SrcLR)
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Your working code should be:
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("Page 1")
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("U1").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("U" & j).Formula = StrIF
    Next j
End With

Set RngFilter = SrcWs.Range("A1:U" & SrcLR)
With RngFilter
    .AutoFilter Field:=21, Criteria1:="0", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy
    Worksheets.Add.Paste
End With
SrcWs.Columns(21).Delete
ActiveSheet.Name = "FilteredSheet"
Set NewSh = Worksheets("FilteredSheet")
Application.CutCopyMode = False
If SrcWs.AutoFilterMode = True Then SrcWs.AutoFilterMode = False
NewSh.Activate
NewSh.Columns(21).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 'change the column number
        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

Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
This update worked perfectly. Thank you!
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Edward! Pleased to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial