How to use VBA to change 0 value to Blank based on criteria in another column

< Back to Search results

 Category: [Excel]  Demo Available 

How to use VBA to change 0 value to Blank based on criteria in another column




In the attached, the user needs VBA code to delete the values in Column K that have a "0" value if the value in column C begins with "1111".

First tab is raw data, second tab is desired results.

Attached is a representative spreadsheet, actual spreadsheet can be many thousands of rows.






Sub ClearColK()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:K" & LastRow).AutoFilter Field:=3, Criteria1:="=1111*"
    Range("A1:K" & LastRow).AutoFilter Field:=11, Criteria1:="0"
    Range("K2:K" & LastRow).SpecialCells(xlCellTypeVisible).ClearContents
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by Mumps.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to count blanks if 1 of 2 conditions are met
How to use conditional format to highlight the blank cells
How to print range excluding blank rows with formulas
How to count non blanks 5 years from the last non blank date


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.




stars (0 Reviews)