We help IT Professionals succeed at work.

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

78 Views
Last Modified: 2017-03-21
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

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

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

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions