VBA replace all filtered values

[mark84]

Hi guys,
I need your support to solve this problem.
My sheet is named "XXX"
In VBA I write the code to filter all values in Column8 expept for 0 value.

Sub step1()
With Sheets("XXX")
.Range("A1:P1").AutoFilter Field:=8, Criteria1:="<>0"
End With


Now I need to replace all these filtered values (without 0) with "5" value.

I tried to use the function "Replace", but in the field "What:=" I don't want to insert a specific value, I want to replace all filtered values.

Could someone help me please?
Thanks in advance

Regards,
Marco

 

[Anfinsen]

Hi guys,
I need your support to solve this problem.
My sheet is named "XXX"
In VBA I write the code to filter all values in Column8 expept for 0 value.

Sub step1()
With Sheets("XXX")
.Range("A1:P1").AutoFilter Field:=8, Criteria1:="<>0"
End With


Now I need to replace all these filtered values (without 0) with "5" value.

I tried to use the function "Replace", but in the field "What:=" I don't want to insert a specific value, I want to replace all filtered values.

Could someone help me please?
Thanks in advance

Regards,
Marco

Hey Mark,
Is there a purpose to filtering the data, rather than just telling VBA to replace any value <> to 0 with a 5, in the given column?

 

[mark84]

I need an automatic process to correct the discounts that my company gets from an external tool.
Can you help me?

 

[abdelfattah]

Hi mark,
my guess like this

Sub step1()
With Sheets("XXX")
.Range("A1:P1").AutoFilter Field:=8, Criteria1:="<>0"
Dim c As Range
For Each c In Range("H2:H4") 'change until   finish last cell contains value in column H 
    If c.Value <> 0 Then c.Value = 5
Next c
 End With
End Sub

 

[Domenic]

Try the following...

Sub step1()

    Dim lastRow As Long
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With

    Dim filteredData As Range
    With Sheets("Sheet1").Range("A1:P" & lastRow)
        .AutoFilter Field:=8, Criteria1:="<>0"
        On Error Resume Next
        Set filteredData = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not filteredData Is Nothing Then
            .Offset(1, 7).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Value = 5
        End If
        .AutoFilter
    End With

End Sub

Hope this helps!

 

[kevin9999]

Another option...

Option Explicit
Sub mark84()
    Dim ws As Worksheet
    Set ws = Worksheets("XXX")
    With ws.Range("H2:H" & ws.Cells(Rows.Count, "H").End(xlUp).Row)
        .Value2 = Evaluate("IF(" & .Address(, , , 1) & "<>0,5,0)")
    End With
End Sub

 

[mark84]

Hi mark,
my guess like this

Sub step1()
With Sheets("XXX")
.Range("A1:P1").AutoFilter Field:=8, Criteria1:="<>0"
Dim c As Range
For Each c In Range("H2:H4") 'change until   finish last cell contains value in column H
    If c.Value <> 0 Then c.Value = 5
Next c
 End With
End Sub

Thanks for your answer.
First of all, the range H in my file should be set in this way: Range("H2:H" & LastRow).
With your code all the values in column H (not only the filtered cells) will become 5.
Is there a way to change only the filtered values?
Thanks in advance

 

[abdelfattah]

First of all, the range H in my file should be set in this way: Range("H2:H" & LastRow).

@Domenic & Kevin did it and their codes are best than me (no loop)
so the question is why you don't answer them and tell them how the code goes?!
for me I will take @kevin9999 's code is really short and efficient &elegant

​

 

[mark84]

It seems all perfect codes.
About @kevin9999's code I don't undestand this part:

With ws.Range("H2:H" & ws.Cells(Rows.Count, "H").End(xlUp).Row)
.Value2 = Evaluate("IF(" & .Address(, , , 1) & "<>0,5,0)")

How does it work Evaluate? Never seen it

Thanks

 

[abdelfattah]

I think you got what you ask for it.
these guys deserve LIKE and mark solved one of them.