none
delete all resource assignments for a project RRS feed

  • Question

  • I'm trying to delete all resource assignments on a project, but my code gets in an endless loop and I have to kill with task manager

    Sub DeleteAllResourceAssignments()
        Dim A As Assignment, ts As Tasks, t As Task, lngAssignCnt As Long,

        Set ts = ActiveProject.Tasks
        lngAssignCnt = 0
        For Each t In ts
            For Each A In t.Assignments
                A.Delete
                lngAssignCnt = lngAssignCnt + 1
            Next A
        Next t
        MsgBox lngAssignCnt & " resource assignments deleted"
    End Sub

    Wednesday, April 9, 2014 6:49 PM

Answers

  • The code worked for me once I removed the trailing comma on the Dim line.

    I would also have IF Not t is Nothing Then in the code to catch any blank rows.

    The other way to delete all assignments is to select the Resource names column and clear it, but that does require the Resource names column to be visible and does the undesirable thing of moving the cursor etc. It is however the easiest way to do it manually!


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, April 9, 2014 8:56 PM
    Moderator

All replies

  • The code worked for me once I removed the trailing comma on the Dim line.

    I would also have IF Not t is Nothing Then in the code to catch any blank rows.

    The other way to delete all assignments is to select the Resource names column and clear it, but that does require the Resource names column to be visible and does the undesirable thing of moving the cursor etc. It is however the easiest way to do it manually!


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, April 9, 2014 8:56 PM
    Moderator
  • You're right.  It wasn't hung in an endless loop.  It was just taking forever.  I kept giving up after 5 or 10 minutes.  I'm not sure how long it took exactly, but it was somewhere between 15 minutes and 1 hour to delete a measly 2900 assignments.  Weird.  Maybe it was recalculating after each deletion.
    Wednesday, April 9, 2014 9:14 PM
  • If you want to turn of calculation when deletes the assignment, and then turn on the calculation include the below code

    Sub DeleteAllResourceAssignments()
        Dim A As Assignment, ts As Tasks, t As Task, lngAssignCnt As Long
        Dim xlCalc As XlCalculation -- new code

        ' turn off calculation or this will take looooog
        xlCalc = Application.Calculation -- new code
        Application.Calculation = xlCalculationManual -- new code

        Set ts = ActiveProject.Tasks
        lngAssignCnt = 0
        For Each t In ts
            For Each A In t.Assignments
                A.Delete
                lngAssignCnt = lngAssignCnt + 1
            Next A
        Next t

      ' turn on calculation 
        Application.Calculation = xlCalc

        MsgBox lngAssignCnt & " resource assignments deleted"
    End Sub



    Sai PMP, PMI-SP, MCTS Project, MVP Project

    Wednesday, April 9, 2014 11:06 PM
    Moderator