Link to home
Start Free TrialLog in
Avatar of Edward Pamias
Edward PamiasFlag 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

Avatar of Shums Faruk
Shums Faruk
Flag of India image

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

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

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Edward Pamias

ASKER

This update worked perfectly. Thank you!
You're Welcome Edward! Pleased to help.