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

 

[mark84]

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

Sure I'll give like for the previuos codes.
But I'd also like to understand what I'm doing

 

[Domenic]

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

Let's assume that the last row in Column H is Row 100. The expression "IF(" & .Address(, , , 1) & "<>0,5,0)" returns something like the following string...

IF([Book1.xlsm]Sheet1!$H$2:$H$100<>0,5,0)

The string is then passed to the Evaluate method, which evaluates the expression and returns a value. Actually, in this case, it returns an array of values, where each value is returned to its corresponding row in Column H.

Note, however, it looks like with this method there's no need to AutoFilter Column H first.

For additional information regarding the Evaluate method, have a look at the following article...

Application.Evaluate method (Excel)

Office VBA reference topic

learn.microsoft.com

Hope this helps!

 

[kevin9999]

@abdelfattah thank you for your support. Sometimes, the best solution can be the one that the OP understands the best - not necessarily the most efficient or concise code. That way, if they need to make adjustments in the future they can readily alter the code to suit. So in this case, the best solution may be @Domenic 's, or mine, or even yours

 

[mark84]

@abdelfattah thank you for your support. Sometimes, the best solution can be the one that the OP understands the best - not necessarily the most efficient or concise code. That way, if they need to make adjustments in the future they can readily alter the code to suit. So in this case, the best solution may be @Domenic 's, or mine, or even yours

I really appreciate your code because is very concise.
I ask you the last favor: could you explain me (step by step) how does it works this part (I read some guide but I can't understand yet):

.Value2 = Evaluate("IF(" & .Address(, , , 1) & "<>0,5,0)")

Also the Address part is not clear: that 1 what means?
I hope you could answer me again so I can use your code next times and suit to different situations.
Thanks a lot in advance

 

[kevin9999]

could you explain me (step by step) how does it works

I'll try.

You can't look at the line .Value2 = Evaluate("IF(" & .Address(, , , 1) & "<>0,5,0)") in isolation - you need to view it in the context of the range it is evaluating. In this case, it is the range H2:H down to the last row the code finds in column H with a value.

.Value2 = simply means that this range's value will equal what follows

Evaluate converts a formula into a value (in this usage)

"IF(" & .Address the formula is a simple IF() formula where the .Address refers to each and every cell in the range already defined earlier in the code

(, , , 1) is simply shorthand for External:=True. The first 3 arguments in the address parameters are ignored (left blank) which are in order (RowAbsolute , ColumnAbsolute , ReferenceStyle , until we reach the parameter of interest (External:=True) which, when set to true (typically 1 = True, 0 = False in VBA), means you can run the macro when a different sheet is active & it will still work.

If you'd like to see a simpler example of Evaluate in action (without the need to use the Address method), on a blank sheet put some values (numbers) in the range A1:B3 then run the following code with the sheet active:

Sub Test()
    Dim a As Variant
    a = Evaluate("A1:A3+B1:B3")
    Range("D1").Resize(UBound(a)).Value2 = a
End Sub

I hope this has helped your understanding.

 

[mark84]

Sorry for the delay, I was sure to have sent my answer.
Thanks a lot for your support! You are the best my friend!
I want to say thanks to other guys too, this is a wonderful community

 

[kevin9999]

Sorry for the delay, I was sure to have sent my answer.
Thanks a lot for your support! You are the best my friend!
I want to say thanks to other guys too, this is a wonderful community

Glad we could help Mark and thanks for the feedback