title: Excel VBA - delete range of cells based on criteria
body: <p>I want to delete a "row-range" of cells and shift them up, if one cell (column K8:Kxxxx) isn't "6-Other miscellaneous Cluster". </p>
  
  <pre><code>With wb2.Sheets("CALC")
      .Range("L8:L" &amp; LastRow3).Formula = "=IF(G8="""","""",CONCATENATE(G8,""/"",VALUE(TEXT(I8,""00#""))))" 'REF'
  End With
  
  deleteIds = Array("OTIF", "2-Stock Availability on Non Stock item", "1-Not in full or rejected", "3-Stock Availability on Stock item", "4-Credit Release after MAD", "5-Actual PGI after planned PGI") ' Put your employee ids in here
  For Each employeeId In Range(ActiveSheet.Range("K8"), ActiveSheet.Range("K8").End(xlDown))
     If Not (IsError(Application.Match(employeeId.Value, deleteIds, 0))) Then
         ActiveSheet.Range("G" &amp; employeeId.Row).Value = ""
         ActiveSheet.Range("H" &amp; employeeId.Row).Value = ""
         ActiveSheet.Range("I" &amp; employeeId.Row).Value = ""
         ActiveSheet.Range("J" &amp; employeeId.Row).Value = ""
         ActiveSheet.Range("K" &amp; employeeId.Row).Value = ""
         ActiveSheet.Range("L" &amp; employeeId.Row).Value = ""
     End If
  Next
  
  lastrow4 = Range("D:D").End(xlDown).Row
  For i = lastrow4 To 1 Step -1
      If IsEmpty(Cells(i, "D").Value2) Then
          Cells(i, "G8:L50000").Delete Shift:=xlShiftUp
      End If
  Next i
  
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  
  MsgBox "DONE!"
  </code></pre>
  
  <p>This is just the part of the macro, where i tried to delete the cells that don't match the criteria. At the moment the macro looks for every possible value except "06-Other..." and clears the content of the cells in range G8:Lxxx. But I can't get it to delete and shift up the blank cells. Hopefully someone can solve my Problem.</p>