Macro is clearing all cells instead of those that equal 0 after a Vlookup

Question

I have a macro that runs and formats a weekly report of aging invoices. It runs a Vlookup against the previous week's file to return the updates input by the Buyers. I have an IFERROR put in so that I don't get #N/A returned. We also have some code written that was intended to change any cells equal to "0," but it's not working.

Instead of changing just the cells equal to 0 to blank, it's clearing ALL cell values, including the updates that were input by the Vlookup. I am nowhere near a code expert, and the person who was helping me with this has taken a new role and is quite busy. I managed to fix a couple other issues with the code, but this has me stumped.

Selection.AutoFilter
ActiveSheet.Range("A1:V" & LastRow).AutoFilter Field:=9, Criteria1:="0"
Range("I2", "I" & LastRow).SpecialCells(xlCellTypeVisible).Select

Set myrange = Selection
If myrange Is Nothing Then

Else
    Selection.ClearContents
    Selection.FillDown
    Selection.AutoFilter
End If

Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Avoid any `Selection`, `Select` and `Activate` statements. — Pᴇʜ, Jan 15 '19 at 15:50

score 0 · Answer 1 · answered Jan 15 '19 at 16:02

I completely agree with PEH's comment about avoiding select statements and the like and further I would add its best to avoid things like Activesheet - instead always specify what sheet you're referring to.

in any case, there are many way to do what you're looking for. One way would be to use the Find method of the Range class, this works much like the find and replace dialogue you get when you press Ctrl+F.

Cells.Copy
Cells.PasteSpecial xlPasteValues ' replace formulas with their result
Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole ' replace "0" with an empty string

score 0 · Answer 2 · answered Jan 15 '19 at 16:12

Try the code below, it will clear the values of all cells with 0 in column "I". Explanations inside the code's comments.

Option Explicit

Sub ClearZerosOnly()

Dim Rng As Range, myRange As Range
Dim LastRow As Long
Dim Sht As Worksheet

Set Sht = ThisWorkbook.Sheets("Sheet1") ' change "sheet1" to your sheet's name
With Sht
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A1").AutoFilter

    Set Rng = .Range("A1:V" & LastRow)
    Rng.AutoFilter Field:=9, Criteria1:="0" ' set the Auto-Filter criteria 

    ' set the Visible range and column I (from row 2, without header)
    Set myRange = Application.Intersect(Rng.SpecialCells(xlCellTypeVisible), .Range("I2:I" & LastRow))
End With

If Not myRange Is Nothing Then myRange.ClearContents

End Sub

Macro is clearing all cells instead of those that equal 0 after a Vlookup

2 Answers2