Work book crash when deleting a value

  • Hi folks

    I have a workbook in which I can add initials or delete them ( in the 'Teachers' column - column C). It actually works fine.

    The initials are numbered, in the next column (D). No header.

    I can delete initials in column C. The numbers will adjust accordingly in Colum D.

    The limit on the initials entered is 20 (in column C).

    My problem:

    I can delete in Column C, ANYWHERE, just by clicking on it, any initial.

    HOWEVER, If I delete the FIRST initial in C3 (number 1 in column D), vba acts up!

    Please help me to fix it.



  • The dynamic range called Teachers becomes invalid when you delete cell C3 since it is tied to that cell.

    Maybe change the Worksheet_SelectionChange code as follows to reset that range when you delete in that column:

     'Move up teachers
      Application.EnableEvents = False
      Rng.Delete Shift:=xlUp
     'Reset the dynamic range
      ActiveWorkbook.Names("Teachers").RefersTo = "=OFFSET(Teachers!$C$3, 0, 0, COUNTA(Teachers!$C:$C))"
      Application.EnableEvents = True

    Also best to add the EnableEvents False/True code otherwise you'll trigger the Worksheet_Change event whenever you delete a cell.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!