How to use VBA to change 0 value to Blank based on criteria in another column
Category: [Excel] Demo Available
How to use VBA to change 0 value to Blank based on criteria in another column
Requirement:
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.
Solution:
Code:
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.