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