I have a macro that cleans up my data sheet in preparation to feed into another program. Part of this cleanup is to copy the source worksheet to a new worksheet then paste all as values to remove lookup values. A separate part of the macro then deletes all rows where the interior color is anything other than yellow (colorindex=6) AND there is no data below the header row. However, this is not working on columns where I previously had a lookup value that output "" when there was no value found.
I tried adding a code to clear all cells where value was "", however that killed performance and made the macro unusable. Is there a way around this.
These are the two blocks that are not playing well together:
'Copy and paste as values to remove lookup references
Cells.Select
Range("A4").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Deletes non yellow interior color columns where no values are present below header
Dim iiCntr As Long
Dim rwsToCheck As Long
Set ws = ActiveSheet
rwsToCheck = 5000
lColumn = 103
For iiCntr = lColumn To 1 Step -1
If WorksheetFunction.CountA(ws.Range(ws.Cells(2, iCntr), ws.Cells(rwsToCheck, iiCntr))) = 0 And Cells(1, iiCntr).Interior.ColorIndex <> 6 Then
ws.Columns(iiCntr).Delete
End If
Next
Bookmarks