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:
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.