Multiple column filter and re-name column value

[Niranjanrajrishi]

Hello,

I need to filter the date in the 2 different columns and re-name the values in the column "O". My below does not work always and it throws error. Sometime the proposed values will not available in the columns and such scenarios the macro should skip and go to next action.

The below does not work and gives error where the filtered value is nil. Please help.

Range("A1").Select
Rws = Cells(Rows.Count, "O").End(xlUp).Row
On Error GoTo 401
ActiveSheet.Range("$A$1:$AO$" & Rws).AutoFilter Field:=15, Criteria1:="<>New creation", Operator:=xlAnd, Criteria2:="<>Modification"
On Error Resume Next
ActiveSheet.Range("$A$1:$AO$" & Rws).AutoFilter Field:=5, Criteria1:=Array( _
"Posting Block", "Purch. block", "Pur. block POrg", "Co.code post.block"), Operator:=xlFilterValues
Set rng = Range(Cells(2, "O"), Cells(Rws, "O")).SpecialCells(xlCellTypeVisible)
rng.Value = "Modification"

 

[NARAYANK991]

Hi ,

Please upload your workbook with the data and the code.

Narayan

 

[Niranjanrajrishi]

I have already mentioned me code and please see the attached work sheet for your reference.

 

[NARAYANK991]

Hi ,

See if this works.

Sub test()
    Dim rng As Range
   
    If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter
    Rws = Cells(Rows.Count, "O").End(xlUp).Row
   
    On Error Resume Next
   
    ActiveSheet.Range("$A$1:$AO$" & Rws).AutoFilter Field:=15, Criteria1:="<>New creation", Operator:=xlAnd, Criteria2:="<>Modification"
    Set rng = ActiveSheet.Range("$A$2:$AO$" & Rws).SpecialCells(xlCellTypeVisible)
    If Not rng Is Nothing Then
      Set rng = Nothing
      ActiveSheet.Range("$A$1:$AO$" & Rws).AutoFilter Field:=5, Criteria1:=Array( _
                                        "Posting Block", "Purch. block", "Pur. block POrg", "Co.code post.block"), Operator:=xlFilterValues
       
      Set rng = Range(Cells(2, "O"), Cells(Rws, "O")).SpecialCells(xlCellTypeVisible)
      If Not rng Is Nothing Then rng.Value = "Modification"
    End If
    ActiveSheet.Cells.AutoFilter
End Sub

Narayan

 

[Niranjanrajrishi]

Wow! Thank you so much! it worked. I appreciate all your efforts! I love to use this forum regularly. Thank you again!